Intervalmatch, Syn table and loop with loosely coupled tables

[#1]

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!

[Oleg_Troyansky]

I think you need to usethe "extended syntax" of intervalmatch, to avoid the synthetic key. If you join not only on dates, but also on ProductGroupID, then it "should" work the way you wanted...

Example:

Inner Join IntervalMatch (Date,Key) Load FirstDate, LastDate, Key resident Key;

[#3]

Using your syntax I get "$Syn 1 = ProductGroupID+StartDate+EndDate".

[#4]

Hi,

Try this

inner join (Product)

Intervalmatch(OrderDate) load StartDate, EndDate resident Product;

Drop Table Orders;

[#5 · Accepted solution]

Got it!:

We need to remove the right join below "ProductHistory" to load it as a table. Then, the slowly changing part should look like:

INNER JOIN Intervalmatch (OrderDate,ProductGroupID ) load StartDate,EndDate,ProductGroupID resident ProductHistory;
LEFT JOIN (Orders) LOAD * Resident ProductHistory;
DROP TABLE ProductHistory;

Thanks to those who gave me input!