Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
JMAROUF
Creator II
Creator II

slowly changin dimension

Hello everyone,

I have some [polices] which change the [intermediaire] and i want to solve this  problem  with SCD using IntervalMtach function, but it gives a lot of records instead of one,i   have joined the table police_H before and after intervalMatch in the attachment,in this example i have just few rows so no problem, but in the project there will be billion of possibilities so it will bug my laptop.

thanks for help;

here my code :

police_H:
NoConcatenate
load Distinct
police,
intermediaire_H,
//rownumber_H,
MIN(date_effet_H) as date_effet_H,
Min(date_echeance_H) as date_echeance_H,
date(Min(date#(date_effet_H,'DD/MM/YYYY'))) as FromDtae,
date(MAX(date#(date_echeance_H,'DD/MM/YYYY'))) as ToDate
resident police_H_tmp group by police,intermediaire_H,rownumber_H order by police;
drop table police_H_tmp;

inner join

IntervalMatch(date_creation) load FromDtae,ToDate resident police_H;


police_H_tmp:
NoConcatenate
load Distinct
police&date(date_creation,'DDMMYYYY') as police_quit,
date_creation as date_creation_H,
police,
intermediaire_H as intermediaire,
FromDtae,
ToDate
resident police_H;
drop table police_H;
rename table police_H_tmp to police_H;

 

 

Labels (2)
5 Replies
Brett_Bleess
Former Employee
Former Employee

Jamal, the best I have is the following Design Blog post that I think should help in this situation:

https://community.qlik.com/t5/Qlik-Design-Blog/Slowly-Changing-Dimensions/ba-p/1464187

Feel free to search the other posts in this area using the following link if the above is now what you need:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

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.
JMAROUF
Creator II
Creator II
Author

Hi @Brett_Bleess , thank you for the anwser, i've already seen these posts, my problem is the number of possibilities, intervalMatch() function match each date with all intervals of all values, in my case i have thousands of intervals, it bugs my michine in the load.

MarkWillems
Contributor III
Contributor III

Hi

IntervalMatch is designed to join multiple dates available in a date table to each record where those dates fit in the to and from date.

The real question here is, what output are you expecting to see?  Because the functionality is working correctly I think, but you are only expecting one record.  What change are you expecting or hoping for from the data after interval match?

Hey, Please don't forget to add kudos, like or mark as a solution if my reply has helped you at all! 🙂
JMAROUF
Creator II
Creator II
Author

Hi,

this solution works for few data, the problem is with lot of intervals, i will have lot of possibilities, which will cause problem in my laptop , i am searching for a solution which mutchs just one date with an interval for a specific value.

JMAROUF
Creator II
Creator II
Author

Hi,

i've joined the issue in the attachement,  with just 1 210 415 dates  and 515 557 intrvals it gives   546 468 405 and then my computer bugs. 

i want to implement the scd type  2,   i've some policies which change the intermediaite code, so instead of linking transaction and policy with POLICY_ID i want to link  them with POLICY_ID&TRANSACTION_DATE as disccribed by @hic  here: https://community.qlik.com/t5/Qlik-Design-Blog/Slowly-Changing-Dimensions/ba-p/1464187 

however this solution bugs my computer because of IntervalMatch possibilities.