Finding First and Last Records in a group

[#1]

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

[rwunderlich · Accepted solution]

How about:

LOAD

Stage, ID,

date(min(Date)) as StartDate,

date(max(Date)) as EndDate

RESIDENT mydata

GROUP BY Stage, ID

;

-Rob

[#3]

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]

use peek as below

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

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

[#5]

Hi Rob,

Thank you so much for your help. Greatly appreciated.

Linda