IntervalMatch with dates

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

Hello!

I'm new to Qlikview and I'm struggling with the intervalMatch function even though I've been reading many examples here but still I can't apply any of them to my need. I'm trying to match the customer credit score on contract level at the moment of origination of the contract. I have a historical table with the customer scores so I want to match it with the contract start date.

I'm attaching an example with the load inline to see if someone could please help me. So far I created the start and end dates to apply the intervalmatch but I found a problem since sometimes the oldest score is older than the contract start so for these cases I would like to take the oldest score in the customer score table.

Here is an example, the code and the qvw:

TEMP_CREDIT_GRADE:
LOAD * INLINE
[ CUSTOMER_CODE,CRR_NOTES, CRR_TIMESTAMP, CRR_RATING
COREVIEWNO-26115, Bisnode , 05-06-2019, B
COREVIEWNO-26115, Bisnode , 05-12-2018, C
COREVIEWNO-26115, Bisnode , 29-11-2016, B
COREVIEWNO-26115, , 22-09-2016, B
COREVIEWNO-26115, , 26-02-2010, A
COREVIEWNO-32746, , 20-03-2019, A
COREVIEWNO-32746, Bisnode , 29-11-2016, C
COREVIEWNO-32746, , 19-10-2015, A
];

CREDIT_GRADE:
LOAD
*,
Date([CRR_TIMESTAMP]) as StartDate,
if([CRR_TIMESTAMP]< Previous([CRR_TIMESTAMP]),Date(Previous([CRR_TIMESTAMP])-1), Date(today())) as EndDate
RESIDENT TEMP_CREDIT_GRADE
ORDER BY [CUSTOMER_CODE],[CRR_TIMESTAMP] DESC;

DROP TABLE TEMP_CREDIT_GRADE;

CONTRACTS:
LOAD * INLINE
[ CUSTOMER_CODE, CONTRACT_ID, LATEST_CUSTOMER_GRADE, CONTRACT_START_DATE,
COREVIEWNO-26115, COREVIEWNO-23885, B, 15-04-2004,
COREVIEWNO-26115, COREVIEWNO-32615, B, 26-01-2009,
COREVIEWNO-26115, COREVIEWNO-33617, B, 22-09-2009,
COREVIEWNO-26115, COREVIEWNO-33666, B, 30-09-2009,
COREVIEWNO-26115, COREVIEWNO-34191, B, 26-02-2010,
COREVIEWNO-26115, COREVIEWNO-39708, B, 10-07-2013,
COREVIEWNO-26115, COREVIEWNO-41054, B, 31-01-2014,
COREVIEWNO-26115, COREVIEWNO-43478, B, 11-03-2015,
COREVIEWNO-26115, COREVIEWNO-44296, B, 08-07-2015,
COREVIEWNO-26115, COREVIEWNO-44590, B, 18-09-2015,
COREVIEWNO-26115, COREVIEWNO-44612, B, 21-09-2015,
COREVIEWNO-26115, COREVIEWNO-45823, B, 07-04-2016,
COREVIEWNO-26115, COREVIEWNO-46181, B, 27-05-2016,
COREVIEWNO-26115, COREVIEWNO-46905, B, 22-09-2016,
COREVIEWNO-26115, COREVIEWNO-57846, B, 28-02-2019,
COREVIEWNO-26115, COREVIEWNO-58892, B, 28-06-2019,
COREVIEWNO-32746, COREVIEWNO-44374, A, 03-08-2015,
COREVIEWNO-32746, COREVIEWNO-45915, A, 21-04-2016,
COREVIEWNO-32746, COREVIEWNO-46017, A, 04-05-2016,
COREVIEWNO-32746, COREVIEWNO-46143, A, 24-05-2016,
COREVIEWNO-32746, COREVIEWNO-46173, A, 26-05-2016,
COREVIEWNO-32746, COREVIEWNO-46741, A, 26-08-2016,
COREVIEWNO-32746, COREVIEWNO-48725, A, 19-04-2017,
COREVIEWNO-32746, COREVIEWNO-48823, A, 03-05-2017,
COREVIEWNO-32746, COREVIEWNO-48984, A, 29-05-2017,
COREVIEWNO-32746, COREVIEWNO-49176, A, 23-06-2017,
COREVIEWNO-32746, COREVIEWNO-49319, A, 06-07-2017,
COREVIEWNO-32746, COREVIEWNO-49322, A, 07-07-2017,
COREVIEWNO-32746, COREVIEWNO-54374, A, 27-06-2018,
COREVIEWNO-32746, COREVIEWNO-54509, A, 03-07-2018,
COREVIEWNO-32746, COREVIEWNO-56105, A, 23-08-2018,
COREVIEWNO-32746, COREVIEWNO-56277, A, 06-09-2018,
COREVIEWNO-32746, COREVIEWNO-56776, A, 30-10-2018,
COREVIEWNO-32746, COREVIEWNO-56826, A, 31-10-2018,
COREVIEWNO-32746, COREVIEWNO-56963, A, 13-11-2018,
COREVIEWNO-32746, COREVIEWNO-57022, A, 19-11-2018,
COREVIEWNO-32746, COREVIEWNO-57323, A, 18-12-2018,
COREVIEWNO-32746, COREVIEWNO-57970, A, 21-03-2019,
COREVIEWNO-32746, COREVIEWNO-58021, A, 28-03-2019,
COREVIEWNO-32746, COREVIEWNO-58226, A, 23-04-2019,
COREVIEWNO-32746, COREVIEWNO-58233, A, 24-04-2019,
];

LEFT JOIN
IntervalMatch(CONTRACT_START_DATE,[CUSTOMER_CODE])
LOAD StartDate, EndDate,[CRR_RATING]
RESIDENT CREDIT_GRADE;

Thanks in advance!!!!!!!!!

topic IntervalMatch with dates in QlikView

IntervalMatch with dates

Re: IntervalMatch with dates