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;
To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question. I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.