Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
SaraiMoH
New Contributor II

IntervalMatch with dates

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:

INTERVALMATCH HELP.JPG

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!!!!!!!!!

1 Reply
Highlighted
Digital Support
Digital Support

Re: IntervalMatch with dates

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:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Scrip...

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/IntervalMatch_(...

Regards,
Brett

To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.