Slowly Changing dimension - IntervalMatch (duplicated date problem

[OmarBenSalem]

Hi all,

I'm currently testing the slowly changing dimension procedure but I found out that my date has been duplicated.

Here is what I have as a source:

 

Here is what I've done in the script:

//**********************Fact table*********************

Fact:

LOAD

    "Date",

    Customer,

    Sales

FROM [lib://SCD/SCD.xlsx]

(ooxml, embedded labels, table is Feuil2);

//**********************Customer table*********************

load *,

if (Situation='Actif',1,0.5) as FlagSituation;

Customer:

LOAD

Customer,

    Situation,

    DateFrom,

    DateTo,

   DATE(DateFrom)&' - '&date(DateTo) as Period

FROM [lib://SCD/SCD.xlsx]

(ooxml, embedded labels, table is Feuil1);

//**********************Bridge table*********************

Bridge:

IntervalMatch("Date")

Load

DateFrom,

DateTo Resident Customer;

//*********************************************************

Left Join(Fact)

load "Date",

"Date" as ExistingDate,

    DATE(DateFrom)&' - '&date(DateTo) as Period  resident Bridge;

drop Table Bridge;

with a typical master calendar.

Now, if you check the data model; you'll find out that the date has been duplicated:

which leads to this kind of stuff : (each value is duplicated)

I'm really confused and hoping for some kind of help !  What am I doing wrong and what should I do to overcome this issue?

Thank you very much !

you'll find the excel file I'm working with

[lironbaram · Accepted solution]

hi

your problem was you didn't use the customer in the interval match function

try this script

//**********************Fact table*********************

Fact:

LOAD

    "Date",

    Customer,

    Sales

FROM [lib://SCD/SCD.xlsx]

(ooxml, embedded labels, table is Feuil2);

//**********************Customer table*********************

load *,

if (Situation='Actif',1,0.5) as FlagSituation;

Customer:

LOAD

Customer,

    Situation,

    DateFrom,

    DateTo,

   DATE(DateFrom)&' - '&date(DateTo) as Period

FROM [lib://SCD/SCD.xlsx]

(ooxml, embedded labels, table is Feuil1);

inner join IntervalMatch("Date",Customer)

Load distinct

DateFrom,

DateTo,Customer Resident Customer;

[OmarBenSalem]

Yes !! You're amazing ! Thank you very much !