Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

2nd Value Group By

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

1 Solution

Accepted Solutions
martinpohl
Valued Contributor II

Re: 2nd Value Group By

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

6 Replies
datanibbler
Esteemed Contributor

Re: 2nd Value Group By

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

martinpohl
Valued Contributor II

Re: 2nd Value Group By

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

sujeetsingh
Honored Contributor III

Re: 2nd Value Group By

You can go with either iteration or the group by function.

datanibbler
Esteemed Contributor

Re: 2nd Value Group By


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 ;-)

Not applicable

Re: 2nd Value Group By

Agree with Nibbler! Didn't know firstsorted value had a rank parameter. Awesome and clean solution

Thank you,

Byron

Not applicable

Re: 2nd Value Group By

Hi Byron, You can Try with Min function like below:

Min(Timestamp,2)

Community Browser