Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Grouping and using multiple lines for computation into next logic

Jasdev
8 - Asteroid

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?

 

StatusMessage referenceTime changedStartEND
9X14:25:21 12/11/2019 14:25
13X14:17:4012/11/2019 14:09 
9Y16:35:38 12/11/2019 16:35
13Y16:34:3512/11/2019 15:09 
5 REPLIES 5
Ladarthure
14 - Magnetar
14 - Magnetar

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
8 - Asteroid
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:

 

StatusMessage referenceTime changedStartEND
9X14:25:21 12/11/2019 14:25
13X14:17:4012/11/2019 14:09 
9Y16:35:38 12/11/2019 16:35
13Y16:34:3512/11/2019 15:09 

 

StatusMessage referenceTime changedStartEND
9X14:25:2112/11/2019 14:0912/11/2019 14:25
13X14:17:4012/11/2019 14:0912/11/2019 14:25
9Y16:35:3812/11/2019 15:0912/11/2019 16:35
13Y16:34:3512/11/2019 15:0912/11/2019 16:35

 

I will take care of the rest.

Ladarthure
14 - Magnetar
14 - Magnetar

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
19 - Altair
19 - Altair

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

 

w.png

 

r.png 

 

Dan

VianneyM
Alteryx
Alteryx

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

Best,
Vianney
Labels