topic Re: slowly changin dimension in QlikView

slowly changin dimension

JMAROUF — Sat, 16 Nov 2024 02:14:08 GMT

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;

Re: slowly changin dimension

Brett_Bleess — Mon, 21 Oct 2019 17:32:22 GMT

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

Re: slowly changin dimension

JMAROUF — Mon, 21 Oct 2019 20:58:57 GMT

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.

Re: slowly changin dimension

MarkWillems — Tue, 22 Oct 2019 13:11:50 GMT

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?

Re: slowly changin dimension

JMAROUF — Tue, 22 Oct 2019 16:50:05 GMT

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.

Re: slowly changin dimension

JMAROUF — Wed, 23 Oct 2019 09:19:14 GMT

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.