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
- 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
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:
- 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.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
Thank you @MarkSnodgrass !..Really appreciate your help!
0 -
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"1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 301 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 103 SQL DataFlows
- 628 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 756 Beast Mode
- 61 App Studio
- 41 Variables
- 695 Automate
- 179 Apps
- 456 APIs & Domo Developer
- 50 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 399 Distribute
- 115 Domo Everywhere
- 277 Scheduled Reports
- 7 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 11 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 110 Community Announcements
- 4.8K Archive