topic First and Last Instance in QlikView

First and Last Instance

Tue, 31 Dec 2013 11:27:12 GMT

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

Re: First and Last Instance

Peter_Cammaert — Tue, 31 Dec 2013 13:17:28 GMT

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

Re: First and Last Instance

Tue, 31 Dec 2013 13:49:44 GMT

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

Re: First and Last Instance

Tue, 31 Dec 2013 13:54:47 GMT

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

Re: First and Last Instance

Tue, 31 Dec 2013 13:58:35 GMT

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?

Re: First and Last Instance

Tue, 31 Dec 2013 13:59:30 GMT

Could you please tell step by step

Re: Re: First and Last Instance

Tue, 31 Dec 2013 14:21:01 GMT

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.

Re: First and Last Instance

Tue, 31 Dec 2013 14:26:43 GMT

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

Re: Re: First and Last Instance

Tue, 31 Dec 2013 14:29:37 GMT

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.

Re: First and Last Instance

Tue, 31 Dec 2013 14:30:58 GMT

Thanks Kai

Re: Re: First and Last Instance

Tue, 31 Dec 2013 14:53:01 GMT

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

Re: First and Last Instance

Thu, 02 Jan 2014 09:40:26 GMT

Hi Kai,

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

Re: Re: First and Last Instance

Thu, 02 Jan 2014 10:10:19 GMT

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

Re: Re: Re: First and Last Instance

Thu, 02 Jan 2014 11:13:20 GMT

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.