topic Re: Lookup value within range of dates, based on user key in QlikView

Lookup value within range of dates, based on user key

deshikas — Wed, 25 Nov 2020 16:16:04 GMT

I'm having some trouble creating a set analysis that check whether a client is active during a certain period of time.
I have 2 date fields which is the Start date and the end Date of the membership:

- DateField_1 = Start Period of membership
- DateField_2 = End Period of membership

Client	Status	StartDate	EndDate	Key
001	ACTIVE	01/01/2016	01/04/2016	001_15/16_07
001	ACTIVE	01/08/2008	30/08/2015	001_08/09_02

In my financial data reporting I need to know whether the client was active during that reporting period.

So I have created the data to suit my fiscal year which starts in July.

Client	Status	StartMonth	EndMonth
001	ACTIVE	001_15/16_07	001_15/16_10
001	ACTIVE	001_08/09_02	001_15/16_02

I require the set analysis to tell me whether the client is Active or Inactive during that period:

Financial reporting
15/16_01 ACTIVE
15/16_02 ACTIVE
15/16_03 INACTIVE
15/16_04 INACTIVE
15/16_05 INACTIVE
15/16_06 INACTIVE
15/16_07 ACTIVE
15/16_08 ACTIVE
15/16_09 ACTIVE
15/16_10 ACTIVE
15/16_11 INACTIVE

Have tried to use Lookup and Interval map with no success.

Inv_Tab:
IntervalMatch (key1)
LOAD Start_Month, End_Month Resident membership;

Please help.

Cheers,

Desh

Re: Lookup value within range of dates, based on user key

Gysbert_Wassenaar — Tue, 10 May 2016 09:26:09 GMT

The values in the fields Key, Start_Month and End_Month are not numbers, but text values. That's why the intervalmatch function can't work with them. You need to use numeric values. Dates would work.

If you have multiple clients then you'll also want to add the client field to the intervalmatch function so the intervals are matched per client:

Inv_Tab:

IntervalMatch(Key_Date, Client)

LOAD

Start_Month_Date,

End_Month_Date,

Client

RESIDENT

membership

;

Re: Lookup value within range of dates, based on user key

Anonymous — Tue, 10 May 2016 10:01:56 GMT

VLOOKUP FUNCTION IN THE QLIKVIEW IS USEFUL BY USING THE LINK

Vlookup expression

Re: Lookup value within range of dates, based on user key

deshikas — Wed, 11 May 2016 04:17:53 GMT

Thanks Gysbert for your help. I changed the fields into date values and added the client ID in the interval match as you suggested.

It worked perfectly.

Thanks so much.

Desh