Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
;
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
;
VLOOKUP FUNCTION IN THE QLIKVIEW IS USEFUL BY USING THE LINK
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