Help with Peek function

[#1]

Hi all

I am a new bee to the qlikview and having trouble with peek function ..

In my situation, I want to extract Start Date and Stop Date from following table as you can see i have only 1 Date column which contain Start/Change/Stop Date which depends on ACTION column :

01 - Start (New drug start)

02 - Change (Change of dose)

03 - Stop ( Drug Stopped)

The tricky bit is that When Action=02 dose is changed so the Change date should become Start date for the current dose and stop date for the previouse dose.

I am really confused with it,,

Any help will be greatly appreciated

ID	Action	Drug	Dose	Date
6FF	01	03	5.00	09/04/2009 00:00:00
6FF	02	03	10.00	15/03/2010 00:00:00
6FF	02	03	5.00	04/07/2011 00:00:00
6FF	02	03	10.00	27/02/2012 00:00:00
6FF	01	04	250.00	10/07/2008 00:00:00
6FF	03	04	125.00	09/04/2009 00:00:00
389	01	01	75.00	02/10/2008 00:00:00
389	01	03	5.00	15/12/2010 00:00:00
389	03	03	10.00	23/03/2011 00:00:00
389	01	04	62.50	30/01/2007 00:00:00
389	03	04	125.00	09/03/2007 00:00:00

Requred Ou

put

ID	Action	Drug	Dose	Start Date	Stop Date

[#2]

Hi Abu,

So i guess you have to make a

TStart:

load distinct

ID, Drug, max(date)

from table

where action = '02'

group by

ID, drug;

this will give you the new start date

TStop:

Do the same with Action = 03 to get Stop date.

You can Concatenate (or Join) both table to get only 1 final table having only 1 start date and 1 stop date by Drug

best regards

Chris

[Gysbert_Wassenaar · Accepted solution]

See attached example.

[#4]

Hi Gysbert

Many Thanks for the Answer, really appreciated