First and Last Instance

[#1]

Hi,

I have one file with the  (AppL_ID, App_Date, Event_TYpe, Evt_DTE) data , Second file has ( Decision Date):

APPL_ID app_date event_type EVT_DTE Decision Date(This date is on separate Sheet)

123 06/09/2013 entered 06/09/2013

123 06/09/2013 analysed 06/09/2013

123 06/09/2013 referral event 06/09/2013 19/09/2013

456 12/09/2013 entered 12/09/2013

456 12/09/2013 analysed 12/09/2013

456 12/09/2013 entered 12/09/2013

456 12/09/2013 analysed 24/09/2013

456 12/09/2013 referral event 24/09/2013 26/09/2013

789 19/09/2013 entered 19/09/2013

789 19/09/2013 referral event 19/09/2013

789 19/09/2013 analysed 19/09/2013

149 24/09/2013 entered 24/09/2013

149 24/09/2013 referral event 24/09/2013 28/09/2013

158 26/09/2013 entered 26/09/2013

158 26/09/2013 analysed 27/09/2013

158 26/09/2013 entered 27/09/2013

158 26/09/2013 analysed 27/09/2013

158 26/09/2013 referral event 27/09/2013

158 30/09/2013 entered 30/09/2013

158 30/09/2013 referral event 30/09/2013

158 30/08/2013 entered 30/08/2013

158 30/08/2013 analysed 04/09/2013

158 30/08/2013 referral event 04/09/2013 09/09/2013

Entered, Anlaysed & Referral Event could be multiple times. I need to calculate as follows:

1). Analysed (Last instance) Date - Entered (First Instance) Date

2). Referral Event (Last instance) Date - Analysed (Last Instance) Date

3). Decisioned Date - Referral Event (Last Instance) Date

After that I need to calculate Average working days for all 3 as above. Could you please tell how and which code should I use as I am newto Qlikview

[Peter_Cammaert]

What do you call 'Last Instance'? Last in time, or last in a row?

Look at the last four records for ID=158. The first one is the most recent referral event, but the last one (almost a month earlier) is the one you want to keep for calculations?

Peter

[#3]

Hi Peter,

First is the earliest one and last is the latest. In 158 exp first referral event is on 04/09/2013 and the last is on 27/09/2013

[#4]

So Last instance is last in time and first instance is first in time

[#5]

Try using one or more of the following expressions in the script in conjunction with "group by":

firstsortedvalue()

FirstValue()

LastValue()

The Help section within QlikView provides a few usage examples.

Are you able to upload the data files?

[#6]

Could you please tell step by step

[#7]

Here is one approach (QVW attached):

Data:

LOAD APPL_ID,

     app_date,

     event_type,

     EVT_DTE,

     DecisionDate

FROM

[SampleData.xlsx]

(ooxml, embedded labels, table is Sheet1);

AnalysedLast:

LOAD

  APPL_ID,

  FirstValue(app_date) as AnalysedLastDate

resident Data

where event_type = 'analysed'

group by APPL_ID

order by app_date desc;

EnteredFirst:

LOAD

  APPL_ID,

  FirstValue(app_date) as EnteredFirstDate

resident Data

where event_type = 'entered'

group by APPL_ID

order by app_date;

... and so on. You can then use the new calculated dates in a table.

The disadvantage of this script approach is that selections (filters) in the dashboard don't affect the values.

[#8]

I need to use Evt_DTE not APp_Date. Also is there any way to sort selection problem.

[#9]

Not a problem. Simply replace app_date with Evt_DTE in my example.

I'll have a look at the selection challenge. It'll need to be on-the-fly rather than calculated once during script execution.

[#10]

Thanks Kai