Filter data by selected values from a list

Question

Tableau version: 2021.2.8

Hello All,

I have a column that has the fee codes listed in comma separated format.

for ex: (1,2,3,4,5,6,7,8)

The requirement is to include contracts with fee codes 1,2,3 or 4.

There were multiple possible combination 1,2 or 2,3 or 3,4 or 3,5 etc...

The requirement is to include contract which has any of these 4 fee codes (1-4)

i.e combination of 1-4 to be included, others to be excluded. ex. 3,5 or 1,6 or 5,6 or 1,2,6 etc..

I am trying CONTAINS and IN without any luck.

Appreciate any suggestion.

Thanks, Pradeep.

Answer 1

If the real problem is similar to your example, the CONTAINS functions should be okay for your purpose. Anyway, if your fee code could be something like (for example) 1 or 10 or 100, you can't use CONTAINS as it would match any of these three cases.

The best approach in my opinion is to start resolving it by transforming the data source. If it is possible, you can apply one of the following instructions:

• Pandas – Split Column by Delimiter
• Splitting a pandas dataframe column by delimiter

If it is not possible, you should design some workaround. I would suggest concatenating every string adding ',' at the beginning at the end of every row, so

1,2,3,4,5,6,7,8,10,100

would become

,1,2,3,4,5,6,7,8,10,100,

This would be very helpful in discriminating 1 from 10 and 100. At this point, you should use the CONTAINS formula (or REGEXP_MATCH) but including the commas. For example CONTAINS(string, ',10,') would match only for 10 and not for 1 or 100.