Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am battling to figure this out. Below is my sample data
Data:
LOAD * INLINE [
ID, HistoryID, TimeStamp
1,1,02/08/2014 14:00:00
1,2,02/08/2014 14:26:00
1,3,02/08/2014 14:56:00
2,4,04/08/2014 11:05:00
2,5,04/08/2014 12:15:00
3,6,06/08/2014 13:00:00
3,7,06/08/2014 13:50:00
3,8,07/08/2014 14:00:00
3,9,07/08/2014 14:26:00
What I want is a table that has the 2nd value for each grouped ID added as a new column
i.e.
ID, HistoryID, TimeStamp, 2ndValue
1,1,02/08/2014 14:00:00,02/08/2014 14:26:00
1,2,02/08/2014 14:26:00,02/08/2014 14:26:00
1,3,02/08/2014 14:56:00,02/08/2014 14:26:00
2,4,04/08/2014 11:05:00,04/08/2014 12:15:00
2,5,04/08/2014 12:15:00,04/08/2014 12:15:00
3,6,06/08/2014 13:00:00,06/08/2014 13:50:00
3,7,06/08/2014 13:50:00,06/08/2014 13:50:00
3,8,07/08/2014 14:00:00,06/08/2014 13:50:00
3,9,07/08/2014 14:26:00,06/08/2014 13:50:00
How do I do this in a simple way.
Please urgently need your help figuring this one out
Cheers,
Byron
Here the script:
Data:
LOAD * INLINE [
ID, HistoryID, TimeStamp
1,1,02/08/2014 14:00:00
1,2,02/08/2014 14:26:00
1,3,02/08/2014 14:56:00
2,4,04/08/2014 11:05:00
2,5,04/08/2014 12:15:00
3,6,06/08/2014 13:00:00
3,7,06/08/2014 13:50:00
3,8,07/08/2014 14:00:00
3,9,07/08/2014 14:26:00
];
left join
load ID,
FirstSortedValue(TimeStamp,HistoryID,2) as SecondTimeStamp
resident Data
group by ID
;
See also attached
Regards
Hi,
you need to build a loop over the group_IDs and then use the max([field], 1) function - is the first thing that comes to my mind. There might be an easier way or you might be able to do it in one pass - but I think that one of the main objectives should always be that others with no more than general IT knowledge should be able to understand your code - so I'd rather do a loop here than a "complicated" construct with WHERE() and GROUP BY and whatnot...
HTH
Best regards,
DataNibbler
Here the script:
Data:
LOAD * INLINE [
ID, HistoryID, TimeStamp
1,1,02/08/2014 14:00:00
1,2,02/08/2014 14:26:00
1,3,02/08/2014 14:56:00
2,4,04/08/2014 11:05:00
2,5,04/08/2014 12:15:00
3,6,06/08/2014 13:00:00
3,7,06/08/2014 13:50:00
3,8,07/08/2014 14:00:00
3,9,07/08/2014 14:26:00
];
left join
load ID,
FirstSortedValue(TimeStamp,HistoryID,2) as SecondTimeStamp
resident Data
group by ID
;
See also attached
Regards
You can go with either iteration or the group by function.
Hi Martin,
that is pretty easy - and straightforward to understand. I'd recommend that. Didn't know yet that FirstSortedValue could also return the second value in a group ...
Again what learned 😉
Agree with Nibbler! Didn't know firstsorted value had a rank parameter. Awesome and clean solution
Thank you,
Byron
Hi Byron, You can Try with Min function like below:
Min(Timestamp,2)