Grouping and using multiple lines for computation into next logic

[Jasdev]

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

[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

[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.

[Ladarthure]

here is a sample workflow, the thing is the dateformat you have may not be recognized by alteryx natively, so you first have to use either a formula or a datetimeparse tool to get a date in datetimeformat and then can agregate the way you want.

[danilang · Accepted solution]

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

[VianneyM]

Hi @Jasdev ,

you could do a join f those two tables using the status, message reference and time changed as key values and then you have the values of file 2 together with the ones of table 1.

after that use a formula tool with a simple if function where you test if the value of column start is empty then take the value start of file 2, else keep the current value. You do the same for the end column 

let me know if you have problems 

best,

vianney