Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!!!!!!!
Here is a Design Blog post you likely did not find:
https://community.qlik.com/t5/Qlik-Design-Blog/IntervalMatch/ba-p/1464547
And here is the Help link on it as well, just in case you have not checked things there:
Regards,
Brett