topic Finding First and Last Records in a group in QlikView

Finding First and Last Records in a group

Thu, 01 Sep 2011 16:26:58 GMT

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

Re: Finding First and Last Records in a group

rwunderlich — Thu, 01 Sep 2011 21:06:32 GMT

How about:

LOAD

Stage, ID,

date(min(Date)) as StartDate,

date(max(Date)) as EndDate

RESIDENT mydata

GROUP BY Stage, ID

;

-Rob

Re: Finding First and Last Records in a group

Fri, 02 Sep 2011 03:05:06 GMT

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

Finding First and Last Records in a group

SunilChauhan — Fri, 02 Sep 2011 08:16:52 GMT

use peek as below

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

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

Finding First and Last Records in a group

Sat, 03 Sep 2011 07:49:41 GMT

Hi Rob,

Thank you so much for your help. Greatly appreciated.

Linda