2 Replies Latest reply: Feb 8, 2017 5:10 AM by omar bensalem RSS

    Slowly Changing dimension - IntervalMatch (duplicated date problem

    omar bensalem

      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