Comparison of data to the immediate previous date for every consecutive date

ozarkram

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

GrantSmith

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"}]}]}

ozarkram

Thank you so much @GrantSmith !..Really appreciate your help!

GrantSmith

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"}]}]}

ozarkram

Thank you so much @GrantSmith !..Really appreciate your help!