Preppin Data — 2019W08

Key Learning

When using Pivot — rows to columns, cannot pivot more than 1 column at once.

Sample Input

Theft Details

Store Names

Solution Steps

Expectation

1. Store Names to be split on ‘-’ and used to join against store ID column from Theft Audit data
2. Fix the type name using group and replace options
3. Pivot row to columns to get theft and inventory fix in different columns
4. Calculate the remaining columns requested for.

Implementation

1. Step 1 and 2 are same as above
2. The Pivot option did not work because there were 3 column I had to aggregate and convert — Quantity and Date against the Action Dimension
3. Another issue I have faced is because the I couldn't drag Action field into the dimension space — May be a bug.
4. Next steps — Duplicate the flow till here to split the data on Action — Theft and Adjustment.
5. Update the column names for Dates and Quantity from both flows and join both flows based on the other fields.
6. Cleanup the duplicate fields and calculated variance on stock adjustment and no. of days taken to implement adjustment.

Sample output

Output Image

Flow Snapshot

Flow Snapshot

Photo by Jelleke Vanooteghem on Unsplash