Forum Discussion

phaering's avatar
phaering
Icon for Helper I rankHelper I
6 years ago
Solved

Create 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)/THE...
Need Help
Show and Tell
Tips and Tricks
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
phaering
Icon for Helper I rankHelper I
6 years ago

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's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Use Alt-Enter

    • phaering's avatar
      phaering
      Icon for Helper I rankHelper I
      6 years ago

      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's avatar
        phaering
        Icon for Helper I rankHelper I
        6 years ago

        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!