Magic ETL

Magic ETL

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

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..


Input

  1. pickdate picknumber Plant
  2. 1/3/2022 L009803 Seattle
  3. 1/3/2022 L009803 Seattle
  4. 1/3/2022 L009803 Seattle
  5. 1/20/2022 L19033 Irving
  6. 1/21/2022 KL89231 Mini chih
  7. 1/23/2022 MY12341 kettle
  8. 1/23/2022 MY12341 kettle
  9. 1/25/2022 FD1211 Seattle
  10. 1/26/2022 HJ12W1 Irving
  11. 2/11/2022 K231245 Irving
  12. 2/11/2022 K231245 Irving
  13. 2/15/2022 N12ER1 Mini chih
  14. 2/21/2022 JS1234 Irving
  15. 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

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


Tagged:

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In

Answers

  • 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:

    1. 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:

    1. 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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Thank you @MarkSnodgrass !..Really appreciate your help!

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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In