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

[#2]

The attached qvw demonstrates both methods: script and on-the-fly.

[#3]

Hi Kai,

Could you please paste qlikview file in the message because unable to open your file until I purchase licence.

[#4 · Accepted solution]

Try the QVW attached. You should be able to open it.

[#5]

Thanks Kai. I can open it but it's giving me error. Basically I have data  two attached files. What would be the code in this case.