topic Re: Help with Peek function in QlikView

Help with Peek function

Tue, 09 Jul 2013 10:57:50 GMT

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

Re: Help with Peek function

Tue, 09 Jul 2013 11:25:27 GMT

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

Re: Help with Peek function

Gysbert_Wassenaar — Tue, 09 Jul 2013 11:58:33 GMT

See attached example.

Re: Help with Peek function

Tue, 09 Jul 2013 12:20:32 GMT

Hi Gysbert

Many Thanks for the Answer, really appreciated