Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
OmarBenSalem
Esteemed Contributor

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
Honored Contributor II

Re: Slowly Changing dimension - IntervalMatch (duplicated date problem

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;

2 Replies
lironbaram
Honored Contributor II

Re: Slowly Changing dimension - IntervalMatch (duplicated date problem

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
Esteemed Contributor

Re: Slowly Changing dimension - IntervalMatch (duplicated date problem

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

Community Browser