Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
OmarBenSalem

Slowly Changing dimension - IntervalMatch (duplicated date problem

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:

Capture.PNG  Capture.PNG

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:

Capture.PNG

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

Capture.PNG

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

1 Solution

Accepted Solutions
lironbaram
Partner - Master III
Partner - Master III

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;

View solution in original post

2 Replies
lironbaram
Partner - Master III
Partner - Master III

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
Author

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