The problem here is that your IF statement will only return a value for the first true criteria. So, for example, your first record contains "I-786", so the if statement will be true and it will return "I-786". But it also includes "AZD78-A". However, the IF statement won't continue onto that test, so it'll never return "AZD78-A". It will only return a single value. My guess is that you want to return multiple values. That's a bit trickier as it would require you to create new records for each label. If interested, there are some hacky ways to make this work. Let me know if you'd like to hear about them.
You may regret about this later but you could use IF CONTAINS and test all your options.
IF CONTAINS([Labels],'I-786') THEN 'I-786'
ELSEIF CONTAINS([Labels],'de-DE') THEN 'de-DE'
ELSEIF CONTAINS([Labels],'AZD78-A') THEN 'AZD78-A'
ELSEIF CONTAINS([Labels],'158') THEN '158'
I say that you may regret because this kind of manipulations in Tableau can cause performance issues.
Do you have access to Tableau Prep? If you do, you can do all these cleaning there and have a perfect dataset to use in Desktop later.
I hope this helps
yes, that explains the behaviour I saw when I was using REGEXP. I would like to hear about the other hacky ways to deal with this issue.
Thanks you for your time,
at the end, that was the temporary solution I used. It works fine, however, my dataset is constantly changing and so I need to keep track
of the changes all the time so I do not forget about a label. That´s why I hoped to come up with a somehow automatic way.
Yes, I do have Tableau Prep and I looked into that but I am not sure how to use it for this usecase.
So, before we look at a hacky method in Tableau, as Rodrigo noted, you can do this in Tableau Prep as follows.
1) Bring in your data.
2) Create a new clean step then make it an automatic split.
This will give you 10 fields splitting the label by commas. If you have more than 10, you can increase the number.
4) Create a pivot step and add all of the split columns you created previously.
When this is done, you'll have a data set that has each label in it's own record.
See attached Tableau Prep flow.
Commas.tfl 3.3 KB
Now here's the hacky way to do it within Tableau, without first using Prep. Like the Prep method, our goal is to get each label in it's own row like this:
ID Blocked Final Label 15 yes 158 15 yes I-786 15 yes New Request 15 yes AZD78-A 15 yes RE-Program-Improvement 85 no RE-Program-Improvement 85 no AZD78-Z 85 no Improvement 85 no 786-Z 85 no 286 786 NA New Request 786 NA 869 786 NA AZD78-A 786 NA I-786
To do this in Tableau, we'll need to first create another data source, with a list of numbers. I created it in Excel.
This will allow us to do something similar to the 10-part split we did in Tableau Prep.
In Tableau, join this new data source to your original data source using a 1=1 join calculation. This will essentially create 10 duplicates of your original data (one for each Label Number).
Now perform a split on Labels.
You should now have 10 new fields just like in Prep.
Next create a calculated field that will pick one of these 10 labels depending on the Label Number.
// Get the label based on the label number
CASE [Label Number]
WHEN 1 THEN [Labels - Split 1]
WHEN 2 THEN [Labels - Split 2]
WHEN 3 THEN [Labels - Split 3]
WHEN 4 THEN [Labels - Split 4]
WHEN 5 THEN [Labels - Split 5]
WHEN 6 THEN [Labels - Split 6]
WHEN 7 THEN [Labels - Split 7]
WHEN 8 THEN [Labels - Split 8]
WHEN 9 THEN [Labels - Split 9]
WHEN 10 THEN [Labels - Split 10]
Now let's build a view and see what we have.
Okay, we're getting there, but we want to filter out the blank labels. Because we're going to use Final Label as a filter in the future, let's duplicate that field into one called Final Label (copy).
Now let's build a view to show how you'd use it.
We'll filter out any blank labels.
Then we'll add Final Label as a filter to allow the user to select labels of interest.
I'm attaching my sample data set and the Tableau workbook.
Note: You will need to be careful with either of these methods as they will duplicate your data. That means that you could run into issue if you're performing any aggregations. If you do run into that, please feel free to open a new post on the forums and we'll help you out.
If this resolves your problem, please be so kind as to mark one of my responses as helpful and/or the "correct answer". This will allow us to close this thread and will make it easier for others to find the solution to similar problems in the future. Thanks!
I have just tried the option with Tableau prep and it seems to be working! Thank you very much!
I will indeed need to perform aggregations and various calculation wiith this data set so I might get back you again!
Thanks a lot!