2 Replies Latest reply on Sep 12, 2018 9:52 PM by garth.conrad

    Data origami - creating rows of data that don't exist

    garth.conrad

      Here is the challenge. I have Parts, FMC (codes), and Dates of events. I need to modify the data set to add blank rows for months where there is no event for a Part and FMC combination.

       

      Count by Date
      PartFMC1/1/20182/1/20183/1/20184/1/2018
      A11111
      21111
      B11null11
      21nullnullnull

       

      First, I know many will respond you don't need to add blank rows because Tableau can account for empty cells and replace nulls with zeros.  99% of the time that works. However, there are a few applications where it doesn't work (blended data with other data). Second, I know the empty rows can be added on the data source side through a complex query or series of queries. Putting all that aside, I am deliberately trying to test Tableau Prep to create the empty rows ("data origami"). I have gotten it to work but am looking for ideas from others on ways of simplifying the flow.

       

      Steps used:

      • Branch 1 - remove the Date field then aggregate the data to get unique combinations of Part and FMC.  Add a field "Link" that has a value of '1'.
      • Branch 2 - remove the Part and FMC fields then aggregate the data to get unique list of Dates.  Add a field "Link" that has a value of '1'.
      • Join the aggregates from the two branches to get a full crossing of all values. (use "Link" field as the join).
      • Clean up the data by removing the "Link" fields.
      • Join with this with the original raw data based Date, Part and FMC fields. Restrict to unique unmatched values only.
      • Join the 'Unmatched values' with the original raw data to create the full set of all values.
      • Clean it up by removing redundant field names.

       

      Ideas for simplifying?

       

      G