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.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 600 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 685 Beast Mode
- 43 App Studio
- 38 Variables
- 654 Automate
- 169 Apps
- 438 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 383 Distribute
- 110 Domo Everywhere
- 267 Scheduled Reports
- 6 Software Integrations
- 111 Manage
- 108 Governance & Security
- 8 Domo University
- 25 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive