topic Re: If and Lookup in QlikView

If and Lookup

Mon, 02 Dec 2013 11:59:23 GMT

Hi,

I am new to qlikiew. I am wondering how can use If and Lookup function together.

Shoaib

Re: If and Lookup

tresB — Mon, 02 Dec 2013 12:08:33 GMT

Could you explain your requirement ?

Re: If and Lookup

Mon, 02 Dec 2013 12:25:08 GMT

Thanks , explanation as below:

App_ID Event_Typ Event Date

12345678 Entered 01/01/2013

12345678 Referral Event 05/01/2013

98765432 Entered 01/01/2013

98765432 Referral Event 05/01/2013

98765432 Entered 07/01/2013

98765432 Referral Event 10/01/2013

So i want to calculate the average no of days of the difference between last instance of Referral event and the first instance of Entered.

Re: If and Lookup

tresB — Mon, 02 Dec 2013 12:52:22 GMT

Not yet very clear to me. I am just thinking aloud, you might have to use intervalmatch() of max(), min() in combination.

Re: If and Lookup

Mon, 02 Dec 2013 13:00:17 GMT

I want to calculate the different between Referral event and entered date. Enetered and Referral event could be multiple times for one APp_ID. So should be the first instance of Entered and the last instance of Referral event. If I get this difference then need to use its average.

Re: If and Lookup

davidrobles — Mon, 02 Dec 2013 13:21:31 GMT

Try

load

App_ID,

Event_Typ as "Start Event",

Event Date as "Start Date"

where Event_Typ=Entered

load

App_ID,

Event_Typ as "End Event·,

Event Date as "End Date"

where Event_Typ= Referral Event

From your Data

Re: If and Lookup

jonathandienst — Mon, 02 Dec 2013 13:26:03 GMT

You need something like this:

Avg(Aggr(Max({<Event_Typ={"Referred*"}>} [Event Date]) - Min({<Event_Typ={"Entered*"}>} [Event Date]), App_ID))

Jonathan

Re: Re: If and Lookup

tresB — Mon, 02 Dec 2013 13:34:52 GMT

May be like attached sample

Edit: Jonathan Dienst is quick! He is right.