Dear All,
I hope you will be able to advise me on my problem, because I've stuck on this for the whole day.
I have a table with field that has following data:
DAYS |
Monday, Tuesday, Computer, Tableau |
Tuesday, Wednesday, Thursday, Tableau |
Linux, Windows, Friday |
I want to find each occurrence of the day (i.e. Monday, Tuesday, Wednesday, Thursday, Friday) in the above column.
In order to achieve this, I've created a calculated field (dimension) with the formula below.
However, as you know IF condition checks only first occurrence of the expression. Therefore, It will find for example Tuesday only in second row, but it won't find in the first.
Obviously there is a solution, to create a calculated field with IF condition for each expression (i.e. Monday, Tuesday, Wednesday, Thursday, Friday). But I would like to avoid to do this, because in my project I will have more than 50 values, that means 50 dimensions. Would you have an idea how to achieve this with only one dimension.
IF CONTAINS([DAYS],"Monday")
then "Monday"
ELSEIF CONTAINS([DAYS],"Tuesday")
then "Tuesday"
ELSEIF CONTAINS([DAYS],"Wednesday")
then "Wednesday"
ELSEIF CONTAINS([DAYS],"Thursday")
then "Thursday"
ELSEIF CONTAINS([DAYS],"Friday")
then "Friday"
END
Thank you
Greg,
Are the number of columns constant at 4 or is there sometimes 20 and sometimes 1? I ask because splitting the field into 4 fields might be the easiest way to go. Then you could evaluate each column/row combination separately. Split a Field into Multiple Fields
Patrick