Need Help: Subtract CurrentRowDateTime - PreviousRowDateTime from same column

Zel

Hello, please help me create this in DOMO. Thank you.

Here are the columns needed:

1. Alarm ID = (given)
2. Alarm Handled Store Time = (given - sort oldest to newest)
3. AHT = with formula, see screenshot below

AHT: currentRow - previousRow (from same column) (see detailed formula below)

the only condition is that: if the computed AHT = 0, then it will just copy the previous calculated AHT.

MarkSnodgrass

You can do this in Magic ETL, use the Rank & Window tile and use the Lag function to get the previous entry, which will create it as a new column. Next, add a Formula tile and subtract the two times. I would suggest using the unix_timestamp function on both datetimes and then subtracting. This will give you the number of seconds difference between the two times. You can then multiply by 60 to get to minutes and multiply by 60 again to get the number of hours.

Hope this helps.

Zel

Hi, @MarkSnodgrass . Apologies, I have some changes with the columns and formulas:

Added column: TechNumber (given)

UPDATED SORTING:

TechNumber - A to Z
Alarm Handled Store Time - Oldest to Newest

Updated Formula shown below

Condition:

If TechNumber is the same with previous row, AHT will be computed,

AHT: (currentRow - previousRow)*24*60

If calculated AHT is zero, it will just copy the previously calculated AHT

ELSE 0

Zel

HI, @MarkSnodgrass, If it's not too much to ask, may we ask for your help in creating sample ETL / json file for it? Apologies, we haven't really touched ETL yet. We would greatly appreciate your help on this one so we can also review and see how it goes. Thank you very much.

MarkSnodgrass

Here is the JSON for the ETL that you would like to put together. You will have to connect the input dataset to your actual dataset and correct any column name differences, but I tried to match your screenshot.

{"contentType":"domo/dataflow-actions","data":[{"name":"DemoAlarmTime.xlsx","id":"986a7935-4eff-4fe7-878f-7fd7c062f2ce","type":"LoadFromVault","gui":{"x":108,"y":156,"color":3238043},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"91adafad-481e-4a95-8403-d11cf696ccbe","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null,"versionWindow":null},{"name":"Rank & Window","id":"9e4c6190-59e3-4b51-9466-f096e1386bd8","type":"WindowAction","gui":{"x":228,"y":156},"dependsOn":["986a7935-4eff-4fe7-878f-7fd7c062f2ce"],"removeByDefault":false,"notes":[],"partitionedAggregation":false,"additions":[{"name":"Prev_AlarmTime","operation":{"type":"OFFSET","operationType":"LAG","column":"Alarm Handled Store Time","amount":"1"}}],"orderRules":[{"column":"Alarm ID","caseSensitive":false,"ascending":true}],"groupRules":[{"column":"Tech Name","caseSensitive":false}]},{"name":"Add Formula","id":"96748ab3-23aa-4407-8418-771dba6a6531","type":"ExpressionEvaluator","gui":{"x":348,"y":156},"dependsOn":["9e4c6190-59e3-4b51-9466-f096e1386bd8"],"removeByDefault":false,"notes":[],"expressions":[{"fieldName":"AHT","expression":"TIME_TO_SEC(TIMEDIFF(Alarm Handled Store Time, Prev_AlarmTime)) / 60\n","settings":null}]},{"name":"Filter Rows","id":"45465bd2-2354-4141-8d93-a81c4bd1e79d","type":"Filter","gui":{"x":468,"y":264},"dependsOn":["96748ab3-23aa-4407-8418-771dba6a6531"],"removeByDefault":false,"notes":[],"filterList":[{"leftField":"AHT","rightValue":{"value":"0","type":"DOUBLE"},"operator":"NE","andFilterList":[]}]},{"name":"Remove Duplicates","id":"4f95962d-8672-411a-a9df-4f046dab110f","type":"Unique","gui":{"x":588,"y":264},"dependsOn":["45465bd2-2354-4141-8d93-a81c4bd1e79d"],"removeByDefault":false,"notes":[],"partitionedAggregation":false,"fields":[{"name":"Tech Name","caseInsensitive":false},{"name":"Alarm Handled Store Time","caseInsensitive":false},{"name":"Prev_AlarmTime","caseInsensitive":false}]},{"name":"Join Data","id":"e0cbb736-9ab8-4a23-ac17-8b645b0fe1ad","type":"MergeJoin","gui":{"x":720,"y":156},"dependsOn":["96748ab3-23aa-4407-8418-771dba6a6531","4f95962d-8672-411a-a9df-4f046dab110f"],"removeByDefault":false,"notes":[],"joinType":"LEFT OUTER","step1":"96748ab3-23aa-4407-8418-771dba6a6531","step2":"4f95962d-8672-411a-a9df-4f046dab110f","keys1":["Tech Name","Alarm Handled Store Time"],"keys2":["Tech Name","Alarm Handled Store Time"],"schemaModification1":[],"schemaModification2":[{"name":"Alarm ID","rename":"Remove Duplicates.Alarm ID","remove":true},{"name":"Tech Name","rename":"","remove":true},{"name":"Alarm Handled Store Time","rename":"","remove":true},{"name":"Prev_AlarmTime","rename":"","remove":true},{"name":"AHT","rename":"Prev_AHT","remove":false}],"partitioningInputId":""},{"name":"Add Formula 1","id":"e08ffe31-a541-44c5-8213-e96c568bc691","type":"ExpressionEvaluator","gui":{"x":840,"y":156},"dependsOn":["e0cbb736-9ab8-4a23-ac17-8b645b0fe1ad"],"removeByDefault":false,"notes":[],"expressions":[{"fieldName":"Final_AHT","expression":"CASE when AHT = 0 and Prev_AHT is not null then Prev_AHT else AHT END","settings":null}]},{"name":"DemoAlartmTime","id":"db45f7aa-361e-4526-b80a-de22bd58d60d","type":"PublishToVault","gui":{"x":972,"y":156},"dependsOn":["e08ffe31-a541-44c5-8213-e96c568bc691"],"removeByDefault":false,"notes":[],"dataSource":{"name":"DemoAlartmTime"},"partitionIdColumns":[],"upsertColumns":[],"retainPartitionExpression":""}]}

Here is what it should look like.

You will want to use the Final_AHT field that is calculated at the end.

Zel

Hi @MarkSnodgrass thank you very much for this. Will explore this and let you know if it works. 😊