Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Finding First and Last Records in a group

I have data that has an ID, a Stage and a Date.

I need to calculate the days for each ID between the Stages.

The data is sorted into the fields and I need to have code to identify the first date in an ID (Lead) and track the date when the Stage changes and then calculate the days difference (which should be a problem).

The difficulty is getting the right functions to access the first and last record in a group.

Any help is gladly appreciated.

Lindab

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

How about:

LOAD

Stage, ID,

date(min(Date)) as StartDate,

date(max(Date)) as EndDate

RESIDENT mydata

GROUP BY Stage, ID

;

-Rob

View solution in original post

4 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

How about:

LOAD

Stage, ID,

date(min(Date)) as StartDate,

date(max(Date)) as EndDate

RESIDENT mydata

GROUP BY Stage, ID

;

-Rob

Not applicable
Author

Thank you - i will try your suggestion today and report back.

Much appreciated.

Linda

-original message-

Subject: - Re: Finding First and Last Records in a group

From: Rob Wunderlich <qliktech@sgaur.hosted.jivesoftware.com>

Date: 01/09/2011 11:06 pm

Rob Wunderlich created the discussion

"Re: Finding First and Last Records in a group"

To view the discussion, visit: http://community.qlik.com/message/145942#145942

SunilChauhan
Champion
Champion

use peek as below

peek('Columnname',-1,'tablename ') for last recard

peek('Columnname',0,'tablename ') for last recard for first record

Sunil Chauhan
Not applicable
Author

Hi Rob,

Thank you so much for your help. Greatly appreciated.

Linda