1 2 Previous Next 15 Replies Latest reply on Oct 2, 2018 6:14 PM by MV V

# If with contains condition

Hi All,

I have data like below.

 OderNumber Name 1 S1 A1 C1 2 A1 C1 3 C1 S1 4 S1

Requirement is to create a filter which has three values:

A1

C1

S1

Once user

Select A1 all the OrderNumber which has A1 in the Names will be the output

1

2

Select C1 all the OrderNumber which has C1 in the Names will be the outputs

1

2

3

Select S1 all the OrderNumber which has S1 in the Names will be the outputs

1

3

4

Creating a Calculated field so that i can use that as a filter

IF

CONTAINS([Name], 'A1') THEN 'A1'

ELSEIF

CONTAINS([Name], 'C1') THEN 'C1'

ELSEIF

CONTAINS([Name], 'S1') THEN 'S1'

END

Problem with the above approach:

If user has selected C1

1 and 2 will not be shown as it is in the first condition

Regards,

Manish Virmani

• ###### 1. Re: If with contains condition

Another approach will be to use parameters like below.

Hope this helps.

Ossai

• ###### 2. Re: If with contains condition

Hi MV,

You can try below steps:

1. Create parameter Select Name with values :A1,C1,S1

2. Create Variable  Flag :

CONTAINS([Name], [Select Name])

Drag this to filter and select "True".

1 of 1 people found this helpful
• ###### 3. Re: If with contains condition

Thank you Okechukwu Ossai for your reply, but Cant use Parameter as User can select multiple values (sorry forgot to mention this).

So it would be a Multiple value Select drop down.

Thanks

Manish Virmani

• ###### 4. Re: If with contains condition

meenu choudhary Thanks for your reply.

But i have to show multiple value drop down. User can select A1 and C1 both (values will count twice but its business logic).

Thanks

Manish Virmani

• ###### 5. Re: If with contains condition

Hi MV,

You need a cross join to achieve that. This will increase the size of your data. So, you need to carefully choose between the cross-join and parameter approaches.

Step 1: Create another data source with the list of all department names. I did this in Excel in a tab called Dept

Step 2: Join this to your main dataset (Sheet1) using a join calculation 1 = 1. This will duplicate the entire dataset for each department.

Step 3: Create calculated field [Dept Filter] as shown below. Put [Dept Filter] on the filter shelf and set to True.

Hope this helps.

Ossai

2 of 2 people found this helpful
• ###### 6. Re: If with contains condition

Thank you for your suggestion Okechukwu Ossai. I will keep this as my last option.

I am reading about dynamic sets. Not sure if that will help me. I am still exploring the best solution. Will give you an update once i have anything better. Thanks!!!

Regards,

MV

• ###### 7. Re: If with contains condition

Hello,

Do you have a limit on the number of values that can be included in the Name field? One other option is to split the field using the space and then pivot it so that your data looks something like:

OrderNumber     Name

1                         A1

1                         S1

1                         C1

2                         A1

2                         C1

That way you'll be able to achieve what you are after by using a filter on the name field.

Cheers

Rob

• ###### 8. Re: If with contains condition

Thank you Rob Palmer..But doing so again is not easy,, if i understood it correctly by doing this i needs to handle other Measures also as they will show multiple times.

Deepak Rai Tushar  More  please help.

Regards,

MV

• ###### 9. Re: If with contains condition

No luck till now.

A request to all the Data Monarch please help and share your inputs how to solve that in Tableau. (not in the back-end data / database).

Need a multiple value selector filter, not a Parameter in the above problem.

Jonathan Drummey Shawn Wallwork , I strongly believe you guys will help and provide a solution for above problem.

Regards,

MV

• ###### 10. Re: If with contains condition

Hi MV,

The suggestion by Rob Palmer is a solid one.

You could prepare a distinct datasource (after Split & Pivot)

just for this view / analysis only.

Any other views could be using your plain old datasource.

Yours,

Yuri

• ###### 11. Re: If with contains condition

Hi All,

Unfortunately, split and pivot is not available at one time.

So instead, duplicate data source with union and merge the dept name.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 12. Re: If with contains condition

Hi MV,

Shin & Yuri & Okechukwu are all really knowledgeable, I can't add anything to their replies.

Fundamentally Tableau works with data like a database does and Tableau likes "tidy data" where there is one attribute per column. Multi-valued cells as described here break that convention, so some sort of effort is necessary to work with them as if they are a single column, whether that is using multiple parameters or preparation of the Tableau data source or even further in the backend.

Jonathan

• ###### 13. Re: If with contains condition

Thank you Shinichiro Murakami for detailed explanation of the Solution.

Thank you Jonathan Drummey for sharing the logic. I totally agree with you and have great respect for all the people who helped me and gave the solution.

Not sure how and why my colleague said it can be done with multiple dynamic sets. I will check with him on Monday.

Regards,

MV

• ###### 14. Re: If with contains condition

Hi MV,

I'm interested in what your colleague is thinking of. I'm not sure what your colleague means by "dynamic sets", that term isn't familiar to me. Tableau presently has four types of sets that I'm aware of:

- manual or constant sets that we create by selecting marks in a view and choosing to add them to a set

- computed sets that are created by right-clicking on a dimension

- combined sets that are created by combining manual and/or computed sets

- user filter sets that are created using the Server->User Filter menu

Tableau v2018.3 adds a new Set Action for views where we can update sets by selecting marks.

In any case a Set is returning a boolean In/Out for each mark showing it is in the set or not. So to get all of the different combinations for 3 variables (S1, A1, C1) in any order with between 1 and 3 variables selected would require six sets, four different variables would require 14 sets, and so on. You could build a parameter to test for each of the combinations but that's not a multi-select parameter, that's a single select for the desired combination and the number of sets could quickly get very unwieldy.

Jonathan

1 2 Previous Next