Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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!
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...
Inner Join IntervalMatch (Date,Key) Load FirstDate, LastDate, Key resident Key;
Using your syntax I get "$Syn 1 = ProductGroupID+StartDate+EndDate".
Hi,
Try this
inner join (Product)
Intervalmatch(OrderDate) load StartDate, EndDate resident Product;
Drop Table Orders;
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!