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 |
Solucionado! Ir para Solução.
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
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.
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
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