4 Replies Latest reply on Apr 16, 2018 6:49 AM by Seojung Nam

    IF calculation to show only applicable data

    Seojung Nam

      Hey everyone,

      I'm very new to Tableau and still learning a lot from the forum.

      Thank you for your time in advance!

       

      So here are my simplified tables:

       

      <Table A>

       

      ProcessGroupProcessName
      Group1ProcessA
      Group1ProcessB
      Group2ProcessC
      Group2ProcessD
      Group3ProcessE
      Group3ProcessF

       

       

       

      <Table B>

      ItemID

      ProcessAProcessBProcessCProcessD
      ProcessE
      ProcessF
      12345678OutstandingCompleteCompleteCompleteCompleteNULL
      12345679CancelledCompleteCompleteNULLOutstandingOutstanding
      12345680NULLNULLOutstandingCancelledNULLOutstanding

       

      My question here is how to make the ProcessName show up in my result table if any of the processes of a certain ProcessGroup is "Outstanding". For example:

       

      ItemID
      Outstanding Process from Group1
      Outstanding Process from Group2Outstanding Process from Group3
      12345678ProcessANULLNull
      12345679NULLNULLProcessE, ProcessF
      12345680NULLProcessCProcessF

       

       

      I tried to create a calculated field using IF and CONTAINS, but it was getting way too much complicated since my actual data has more than 20 processes.

       

      For example, this is for the column "Outstanding Process from Group1":

      IF CONTAINS([ProcessA], "Outstanding") and not CONTAINS([ProcessB], "Outstanding")

      THEN "ProcessA"

      ELSEIF CONTAINS([ProcessA], "Outstanding") and CONTAINS([ProcessB], "Outstanding")

      THEN "ProcessA, ProcessB"

      ELSEIF not CONTAINS([ProcessA], "Outstanding") and CONTAINS([ProcessB], "Outstanding")

      THEN "ProcessB"

      ELSE NULL

       

      Again, please let me know if there is any other way to get the similar result as above not by writing this manual IF statements some hundred times.

       

       

      Thank you so much!!

        • 1. Re: IF calculation to show only applicable data
          Eric Hammond

          Hi Seojung,

           

          The calculation will be simpler if you first pivot these data.  On the data source tab, for Table B, control-click all field names for processes (ProcessA, ProcessB, etc.), then right-click and select "pivot".  Rename "Pivot Field Names" to "ProcessName", and rename "pivot field values" to "Process_Status".  Now the IF calculation can be avoided entirely.  In a new worksheet:

          • Drag ItemID to the Rows shelf.
          • Drag ProcessGroup to the Columns shelf.
          • Drag ProcessName to the text tile on the Marks shelf.
          • Drag Process_status to the filters shelf and filter for 'Outstanding'.
          • Resize the columns to show longer process names.
          1 of 1 people found this helpful
          • 2. Re: IF calculation to show only applicable data
            Seojung Nam

            Thanks so much Eric!

            It's working well except for the ProcessGroup part! - I couldn't figure out how to join Table A and B.

            It does not allow me to do the join because the ProcessNames are in rows (in Table A) but in column headers (in Table B).

            Is there any way I can group the fields in Pivot or possibly join table A and B?

            • 3. Re: IF calculation to show only applicable data
              Eric Hammond

              Hi Seojung,

               

              Try data blending.  Add table A as a separate connection.  Its field "processName" is already the same as the renamed pivoted field "ProcessName", so Tableau should recognized these as the same field.  By default there will be a broken link symbol next to processname when you select Table A on the data tab of the worksheet.  Just click that link to turn it orange, and then drag Processgroup into your viz.  I haven't recreated your sample data to verify this with a workbook, but this is the approach that I would take to try to solve for the JOIN issue.

              1 of 1 people found this helpful