Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
MVP & Luminary
MVP & Luminary

Re: Finding First and Last Records in a group

How about:

LOAD

Stage, ID,

date(min(Date)) as StartDate,

date(max(Date)) as EndDate

RESIDENT mydata

GROUP BY Stage, ID

;

-Rob

4 Replies
MVP & Luminary
MVP & Luminary

Re: Finding First and Last Records in a group

How about:

LOAD

Stage, ID,

date(min(Date)) as StartDate,

date(max(Date)) as EndDate

RESIDENT mydata

GROUP BY Stage, ID

;

-Rob

Not applicable

Re: Finding First and Last Records in a group

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

chauhans85
Esteemed Contributor

Finding First and Last Records in a group

use peek as below

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

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

Not applicable

Finding First and Last Records in a group

Hi Rob,

Thank you so much for your help. Greatly appreciated.

Linda

Community Browser