Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
SaraiMoH
Contributor II
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
Brett_Bleess
Support (Former)
Support (Former)

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