Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
How about:
LOAD
Stage, ID,
date(min(Date)) as StartDate,
date(max(Date)) as EndDate
RESIDENT mydata
GROUP BY Stage, ID
;
-Rob
How about:
LOAD
Stage, ID,
date(min(Date)) as StartDate,
date(max(Date)) as EndDate
RESIDENT mydata
GROUP BY Stage, ID
;
-Rob
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
use peek as below
peek('Columnname',-1,'tablename ') for last recard
peek('Columnname',0,'tablename ') for last recard for first record
Hi Rob,
Thank you so much for your help. Greatly appreciated.
Linda