Comparison of data to the immediate previous date for every consecutive date
Hi All,
Hope you are doing well!..I am trying to build a Magic ETL workflow to calculate the number of entries (combination of sno and mmodel) occuring new in the current date of reference in comparison to the immediate previous day.. Please find the sample data below:
Input table
sno mmodel snapshot_date
r123 S1234 3/1/2022
r3421 S1234 3/1/2022
y123 D2123 3/1/2022
g21q D2123 3/1/2022
W321 G345 3/1/2022
E231 G345 3/1/2022
r123 S1234 3/2/2022
D891 S1234 3/2/2022
y123 D2123 3/2/2022
W321 G345 3/2/2022
K1231 J1231 3/2/2022
r123 S1234 3/3/2022
D891 S1234 3/3/2022
H213 V231 3/3/2022
121k N213 3/3/2022
And the output required would be
date new Delay
3/1/2022 0
3/2/2022 2
3/3/2022 2
In the above output for the date March 2nd the combination of entries (D891,S1234) and (K1231,J1231) appears only on march 2nd and not on March 1st so the number of new delays would be 2
In the above output for the date March 3rd the combination of entries (H213,V231) and (121K,N213) appears only on March 3rd and not on March 2nd so the number of new delays would be 2..
Can you please let me know how to set up the ETL for the above..
Thanks,
Arun
Best Answers
-
You can utilize a rank and window tile within Magic ETL to do this with the LAG function.
Partition by SNO and MMODEL and Order By your date field with a lag value of 1. This will give you the prior record's date where you can then feed it into a formula tile or a date tile to calculate the number of days between the two dates.
Alternatively you can copy and paste this code into your Magic ETL 2.0 canvas and it should put the two tiles in automatically for you with everything populated.
{"contentType":"domo/dataflow-actions","data":[{"name":"Rank & Window","id":"9123cd28-6687-43eb-bf78-f0b16293897e","type":"WindowAction","gui":{"x":168,"y":180},"dependsOn":["b3589ae1-28a5-4435-a6d0-fd85afdcdc22"],"removeByDefault":false,"notes":[],"partitionedAggregation":false,"additions":[{"name":"Prior Date","operation":{"type":"OFFSET","operationType":"LAG","column":"snapshot_date","amount":"1"}}],"orderRules":[{"column":"snapshot_date","caseSensitive":false,"ascending":true}],"groupRules":[{"column":"sno","caseSensitive":false},{"column":"mmodel","caseSensitive":false}]},{"name":"Date Operations","id":"67aeca27-eade-480c-bac3-a117fa03ffbf","type":"DateCalculator","gui":{"x":300,"y":180},"dependsOn":["9123cd28-6687-43eb-bf78-f0b16293897e"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Days Delay","calcType":"DATE_DIFF","fieldA":"snapshot_date","fieldB":"Prior Date"}]}]}
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Thank you so much @GrantSmith !..Really appreciate your help!
0
Answers
-
You can utilize a rank and window tile within Magic ETL to do this with the LAG function.
Partition by SNO and MMODEL and Order By your date field with a lag value of 1. This will give you the prior record's date where you can then feed it into a formula tile or a date tile to calculate the number of days between the two dates.
Alternatively you can copy and paste this code into your Magic ETL 2.0 canvas and it should put the two tiles in automatically for you with everything populated.
{"contentType":"domo/dataflow-actions","data":[{"name":"Rank & Window","id":"9123cd28-6687-43eb-bf78-f0b16293897e","type":"WindowAction","gui":{"x":168,"y":180},"dependsOn":["b3589ae1-28a5-4435-a6d0-fd85afdcdc22"],"removeByDefault":false,"notes":[],"partitionedAggregation":false,"additions":[{"name":"Prior Date","operation":{"type":"OFFSET","operationType":"LAG","column":"snapshot_date","amount":"1"}}],"orderRules":[{"column":"snapshot_date","caseSensitive":false,"ascending":true}],"groupRules":[{"column":"sno","caseSensitive":false},{"column":"mmodel","caseSensitive":false}]},{"name":"Date Operations","id":"67aeca27-eade-480c-bac3-a117fa03ffbf","type":"DateCalculator","gui":{"x":300,"y":180},"dependsOn":["9123cd28-6687-43eb-bf78-f0b16293897e"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Days Delay","calcType":"DATE_DIFF","fieldA":"snapshot_date","fieldB":"Prior Date"}]}]}
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Thank you so much @GrantSmith !..Really appreciate your help!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive