slowly changin dimension

[JMAROUF]

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;

[Brett_Bleess]

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

[JMAROUF]

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]

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?

[JMAROUF]

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]

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.