Help with Magic ETL to calculate the distinct count of pickup numbers by plant

ozarkram

Hi All,

I am trying to get the distinct count of pickup numbers by plant and pickupmonth year..Please find my input table below...Can you please help me with the Magic ETL for the same...I am also attaching the excel file data..

Sample Data.xlsx

Input

pickdate    picknumber  Plant
1/3/2022    L009803 Seattle
1/3/2022    L009803 Seattle
1/3/2022    L009803 Seattle
1/20/2022   L19033  Irving
1/21/2022   KL89231 Mini chih
1/23/2022   MY12341 kettle
1/23/2022   MY12341 kettle
1/25/2022   FD1211  Seattle
1/26/2022   HJ12W1  Irving
2/11/2022   K231245 Irving
2/11/2022   K231245 Irving
2/15/2022   N12ER1  Mini chih
2/21/2022   JS1234  Irving
2/25/2022   MK12E1  Kettle

I am looking to get to the below table to get the distinct count of pickup numbers by plant and timeperiod

Time period     Seattle Irving  Mini chih   Kettle
Jan-22              2     2        1           1
Feb-22              0     2        1           1

MarkSnodgrass

You can do this with a formula tile and a group by tile. In your formula tile, create a column called TimePeriod and use the LAST_DAY() function to normalize your dates to a single day in each month so that you can group by month. Your formula would look like this:

LAST_DAY(pickdate)

Add a group by tile and group by TimePeriod. Create an aggregated column for each plant and use the Add Formula to create the aggregation. For example, create an aggregated column called Seattle and have your formula look like this:

COUNT(DISTINCT CASE WHEN `plant` = 'Seattle' THEN `picknumber` END)

Do this for each plant and you will have a distinct count for each location for each month.

Hope this helps.

ozarkram

Thank you @MarkSnodgrass !..Really appreciate your help!

jaeW_at_Onyx

instead of materializing this using a bunch of CASE statements consider just building a pivot table card in Analyzer.