Grouping and using multiple lines for computation into next logic

Hi, I am unable to understand how to process this, neither could find anything related to it. For below scenario, i need to group based on message reference first, then pick the date from start column (which I have processed it to be empty for status 9 and some value for status 13) and then other way pick end date from that column and find the differece. I have a logic for finding the difference between 2 dates just need to know how will I incorporate the group by and subtract 2 different columns.

PS. I want to do is, For each message reference, say X, pick start date from status 13 and end date from status 9 and subtract them. I got the logic for date subtraction so for that I need both dates in 1 line. Any idea?

Status	Message reference	Time changed	Start	END
9	X	14:25:21		12/11/2019 14:25
13	X	14:17:40	12/11/2019 14:09
9	Y	16:35:38		12/11/2019 16:35
13	Y	16:34:35	12/11/2019 15:09

Data Investigation

Accepted answers

danilang

Hi @Jasdev

@Ladarthure's solutions are excellent as always, but if you just want the solution to your second question, this will work without requiring any field conversions

Dan

GroupAndFill.yxmd

All comments

Ladarthure

hi @Jasdev,

here is a solution to do so, in fact 2 solutions,

the first you use a summarize tool to get the data you need and then use filters to get the start date and end date for each lines with filters, finally a join to get the data you need.

The second, you make a false start date in 2099 to be able to use summarize with min and max functions to get the dates.

Hope it helped

sample.yxmd

Jasdev

It isn't working as desired when other columns come into picture. I masked the data just to give an example but the actual data contains more than 20 records.

To simplify, instead is there any way to just merge 2 date fields say:

Status	Message reference	Time changed	Start	END
9	X	14:25:21		12/11/2019 14:25
13	X	14:17:40	12/11/2019 14:09
9	Y	16:35:38		12/11/2019 16:35
13	Y	16:34:35	12/11/2019 15:09

Status	Message reference	Time changed	Start	END
9	X	14:25:21	12/11/2019 14:09	12/11/2019 14:25
13	X	14:17:40	12/11/2019 14:09	12/11/2019 14:25
9	Y	16:35:38	12/11/2019 15:09	12/11/2019 16:35
13	Y	16:34:35	12/11/2019 15:09	12/11/2019 16:35

I will take care of the rest.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703