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

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


      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


      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?