4 Replies Latest reply: Jul 29, 2010 1:19 PM by Patrick Slama RSS

    Intervalmatch, Syn table and loop with loosely coupled tables

      Hi,

      I have the following issue and feel the solution is close but simply can get it.

      I am trying to build a report on Part Numbers. Part Numbers are parts of ProductID, which then are parts of ProductGroupID. The Orders table gives the OrderDate and the ProductGroupID. The ProductHistory table gives which ProductID belongs to which ProductGroupID between which periods (StartDate and EndDate). The Product table gives the ProductID and the PartName.

       


      Product:
      LOAD ProductID,
      PartName;
      LOAD * INLINE [
      ProductID, PartName
      1, A
      1, B
      1, C
      1, D
      1, E
      2, F
      2, G
      3, H
      3, I
      3, J
      3, K
      4, L
      4, M
      4, N
      ];

      ProductHistory:
      RIGHT JOIN (Product)
      LOAD ProductID,
      ProductGroupID,
      StartDate,
      EndDate;
      LOAD * INLINE [
      ProductID, ProductGroupID, StartDate, EndDate
      1, 11, 01.01.2009, 31.10.2009
      1, 12, 01.11.2009, 31.12.2009
      2, 21, 01.01.2009, 31.12.2009
      3, 31, 01.01.2009, 30.06.2009
      3, 11, 01.07.2009, 31.07.2009
      3, 32, 01.08.2009, 31.12.2009
      4, 41, 01.01.2009, 31.01.2009
      4, 11, 01.02.2009, 31.03.2009
      4, 12, 01.04.2009, 31.07.2009
      4, 21, 01.08.2009, 31.08.2009
      4, 31, 01.09.2009, 31.10.2009
      4, 11, 01.11.2009, 31.01.2009
      ];

      Orders:
      LOAD OrderDate,
      ProductGroupID;
      LOAD * INLINE [
      ProductGroupID, OrderDate
      11, 15.01.2009
      12, 15.11.2009
      21, 15.01.2009
      31, 15.01.2009
      11, 15.07.2009
      32, 15.08.2009
      41, 15.01.2009
      11, 15.02.2009
      12, 15.04.2009
      21, 15.08.2009
      31, 15.09.2009
      11, 15.11.2009
      11, 15.02.2009
      12, 15.12.2009
      21, 15.02.2009
      31, 15.02.2009
      11, 15.08.2009
      32, 15.09.2009
      41, 15.02.2009
      11, 15.03.2009
      12, 15.05.2009
      21, 15.09.2009
      31, 15.10.2009
      11, 15.12.2009
      ];

      // Slowly Changing Dimension

      Intervalmatch (OrderDate) load StartDate,EndDate resident Product;


      This produces a loop and QV complains. And I get a syn table which I don't want.

      if I add "left join" before the intervalmatch, I get rid of the loop but still have the syn table.

      If I add


      Left join (Product)
      LOAD * resident Product;
      drop table Product;


      as seen on some post, this isn't the solution neither.

      Thanks for help!