Forum Discussion
Helper ICreate Column
How do I create a new column that uses a formula to generate a new value based on partial values from two other columns? I was able to do this very easily in Tableau using a lengthy IF(CONTAINS)/THEN and ELSEIF(CONTAINS)/THEN formula. What I’m trying to do is create a new, normalized (simplified) name for the operating system value from my source file. I have a long list of servers and their associated OS value. The value is from a polling agent which can provide multiple variations for the exact same OS. Here is a snippet from my Tableau script that works great:
//AIX
IF CONTAINS([OS], "AIX 5.3") THEN "AIX 5.3"
ELSEIF CONTAINS([OS], "AIX 6.1") THEN "AIX 6.1"
ELSEIF CONTAINS([OS], "AIX 7.1") THEN "AIX 7.1"
ELSEIF CONTAINS([OS], "AIX 7.2") THEN "AIX 7.2"
ELSEIF CONTAINS([OS], "AIX VIOS 6.1") THEN "AIX 6.1"
ELSEIF CONTAINS([OS], "AIX VIOS 7.2") THEN "AIX 7.2"
//Windows
ELSEIF CONTAINS([OS], "Win10") AND CONTAINS([OS - Full Name], "Pro") THEN "Win 10 Pro"
ELSEIF CONTAINS([OS], "Win10") AND CONTAINS([OS - Full Name], "Ent") THEN "Win 10 Ent"
ELSEIF CONTAINS([OS], "Win10") THEN "Win 10"
ELSEIF CONTAINS([OS], "Win2000") THEN "Win 2000"
ELSEIF CONTAINS([OS], "Win2003") AND CONTAINS([OS - Full Name], "Data") THEN "Win 2003 DC"
ELSEIF CONTAINS([OS], "Win2003") AND CONTAINS([OS - Full Name], "Standard") THEN "Win 2003 Std"
ELSEIF CONTAINS([OS], "Win2003") AND CONTAINS([OS - Full Name], "Enterprise") THEN "Win 2003 Ent"
ELSEIF CONTAINS([OS], "Win2003") AND CONTAINS([OS - Full Name], "Web") THEN "Win 2003 Web"
ELSEIF CONTAINS([OS], "Win2003") THEN "Win 2003 Std"
ELSE “(unknown)”
END
I would call this new column “OS Best”
Thank you!
Well, that's frankly a horrible construction, doesn't Tableau have a SWITCH/CASE statement?
Use a SWITCH(TRUE()...) statement. Replace CONTAINS with SEARCH or FIND like:
Column?
SWITCH(TRUE(),
SEARCH("AIX 5.3",[OS],,-1 ) <> -1 THEN "AIX 5.3",
SEARCH("AIX 6.1",[OS],,-1 ) <> -1 THEN "AIX 6.1",
...
)
9 Replies
- Greg_Deckler
Community Champion
Well, that's frankly a horrible construction, doesn't Tableau have a SWITCH/CASE statement?
Use a SWITCH(TRUE()...) statement. Replace CONTAINS with SEARCH or FIND like:
Column?
SWITCH(TRUE(),
SEARCH("AIX 5.3",[OS],,-1 ) <> -1 THEN "AIX 5.3",
SEARCH("AIX 6.1",[OS],,-1 ) <> -1 THEN "AIX 6.1",
...
)
- Greg_Deckler
They probably don't need the <> -1, only SEARCH statements should do because they return blank if not found to be considered FALSE so, save yourself some writing. So
Column?
SWITCH(TRUE(),
SEARCH("AIX 5.3",[OS]) THEN "AIX 5.3",
SEARCH("AIX 6.1",[OS]) THEN "AIX 6.1",
...
)
phaeringHow do I write a formula where I can hit <ENTER> so I can have one row per "search"? When I try to create a column it seems like I am being limited to writing the entire formula on one line. Thank you!
- phaering
Thank you! What do the -1 values represent in this formula (How do I read that full formula in words)? Also, how do I include the AND aspect if I need to search/compare values in two separate columns to create my new value?
- Greg_Deckler
Sure, SEARCH and all other DAX functions are defined here:
https://docs.microsoft.com/en-us/dax/search-function-dax
Also, intellisense will tell you what is what when typing DAX formulas. But in SEARCH's case, first see my second post because you don't need the -1 I believe, but SEARCH has the format:
SEARCH( <search text>, <within text>, <start position>, <alternate return value if not found> )
So, let's tackle the second part, the SWITCH(TRUE()...) construct allows you to have any DAX that returns a logical value on the left side, so you read the SWITCH like:
SWITCH(
TRUE(),
<logical condition 1>, <return value 1 if logical condition 1 is true>,
<logical condition 2>, <return value 2 if logical condition 2 is true>,
... (as many of these as you want)
<default return value optional>
)
Now, logical conditions can include && and || for AND and OR so:
SWITCH(TRUE(),
SEARCH("AIX 5.1") || SEARCH("AIX 6.3") || SEARCH("AIX 99.83"),"AIX",
for example. Or any combination of logic. You can use parens like:
( this || that) && this other thing
for example.