5 Replies Latest reply on Sep 1, 2018 4:30 PM by Nitin Paighowal

    Overlapping group members

    Paul Hunt

      Hi guys,

       

      I'm looking to represent some data in a line chart. I am trying to give entries a label according to whether or not a string is in one of their variables.

       

      As a toy example:

       

      Name          Age               Colour

      Anna          12                 Blue; Orange    
      Bill               13              Blue
      Charlie          12               Red
      Doug               14               Blue; Orange; Red
      Eloise               11               Green

       

      So I want to visualise individual colours as well as visualising all colours, so I want the groupings:

       

      Blue = [A, B, D]
      Orange = [A, D]
      Red = [C, D]
      Green = [E]
      All  = [A, B, C, D, E]

       

      I am currently using a calculated field and IF CONTAIN

       

      IF CONTAINS([Colour], "Blue") THEN "Blue"

      ELSEIF CONTAINS([Colour], "Orange") THEN "Orange"

      ELSEIF CONTAINS([Colour], "Red") THEN "Red"

      ELSEIF CONTAINS([Colour], "Green") THEN "Green"

      END

       

      However the Orange category does not get formed because all the members Blue takes them because it comes first in the command.

       

      How do I create overlapped groups?

      And how do I create an "All" group without specifying the colours? (the actual data set contains LOADS of strings that I couldn't possibly manually type out).

       

      Thanks a lot for your help

        • 1. Re: Overlapping group members
          Jim Dehner

          Paul

          Try splitting the the "color" dimension into separate columns and then do and then pivot the data to create individual reocrds

           

          Conditional statements will test each record 1 time only - as soon as a True is returned on the IF side the Then clause is executed and the next record is processed

           

           

          Jim

          • 2. Re: Overlapping group members
            Ankit Bansal

            Paul,

             

            You need to pivot your data and convert in below form:

             

            Name          Age               Colour

            Anna          12                 Blue

            Anna          12                  Orange    
            Bill               13              Blue
            Charlie          12               Red
            Doug               14               Blue

            Doug               14               Orange

            Doug               14               Red
            Eloise               11               Green

             

            Then your formula will work.

             

            Thanks,

            Ankit Bansal

            • 3. Re: Overlapping group members
              Paul Hunt

              Hi Jim,

               

              Thanks for your reply.

               

              Unfortunately that isn't practical. The above is a toy example, in reality my dataset includes 800,000 entries and the "colour" variable often contains lots of derivations including up to 10 different "colours".

               

              I need a IF CONTAINS type calculation which doesn't enforce mutual exclusivity of having 1 entry in 1 group but rather 1 entry can be classified in multiple groups.

              • 4. Re: Overlapping group members
                Paul Hunt

                Hi Ankit,

                 

                Thanks for your reply.

                 

                Unfortunately that isn't practical. The above is a toy example, in reality my dataset includes 800,000 entries and the "colour" variable often contains lots of derivations including up to 10 different "colours".

                 

                I need a IF CONTAINS type calculation which doesn't enforce mutual exclusivity of having 1 entry in 1 group but rather 1 entry can be classified in multiple groups.

                • 5. Re: Overlapping group members
                  Nitin Paighowal

                  this is more of data wrangling work which needs to be done outside Tableau . Try using REGEX outside Tableau wherever you use ETL stuff