Create Column

[phaering]

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!

[Greg_Deckler · Accepted solution]

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",

...

)

[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.

[phaering]

I'm still having problems with instances where I need to search for values in two separate columns to create the value in my new column.  This formula works great for single column searches:

SEARCH("Linux Red Hat Enterprise Server 7.8",VW_LCM_BF_SERVERS[OS],,-1) <> -1,"Red Hat 7.8",

But when I need to also search another column to ensure I'm providing the correct value I am not getting the result I need.  I am using this formula:

SEARCH("Win2008 ",VW_LCM_BF_SERVERS[OS],,-1) && SEARCH("Standard",VW_LCM_BF_SERVERS[OS_FULL_NAME],,-1) <> -1,"Win 2008 Std",

It is putting my result in rows where the 'OS' is not absolutely equal to "Win2008 " (that is a deliberate space after the 8).  For example, the 'OS' is "Win2008R2".  How do I write the formula to only put the desired result in the rows where both search values are met exactly?

Thank you!

[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",

...

)

[phaering]

How 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!

[Greg_Deckler]

Use Alt-Enter

[phaering]

I created a quick sample of my entire script and received an error message:  "Function 'SWITCH' does not support comparing values fo type True/False with values of type Integer.  Consider using the VALUE or FORMAT function to convert one of the values."

I have no idea what this means or how to fix it.

Thank you!

[phaering]

Here is the view of what I have entered so far:

Column = SWITCH(TRUE(),

//AIX

SEARCH("AIX 5.3",[OS],"AIX 5.3",

SEARCH("AIX 6.1",[OS],"AIX 6.3",

SEARCH("AIX 7.1",[OS],"AIX 7.1",

SEARCH("AIX VIOS 6.1",[OS],"AIX 6.1",

//HPUX

SEARCH("HP-UX B.11.11",[OS],"HPUX 11.11",

SEARCH("HP-UX B.11.23",[OS],"HPUX 11.23",

SEARCH("HP-UX B.11.31",[OS],"HPUX 11.31",

//Linux Other

SEARCH("CentOS 4.8",[OS],"CentOS 4.8",

SEARCH("CentOS 5.8",[OS],"CentOS 5.8",

SEARCH("CentOS 6.0",[OS],"CentOS 6.0",

SEARCH("CentOS 6.10",[OS],"CentOS 6.10",

"(unknown)"

)

This is where I get the error message.  Thank you!