Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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!

1 Solution

Accepted Solutions
Not applicable

Intervalmatch, Syn table and loop with loosely coupled tables

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!

4 Replies
MVP
MVP

Intervalmatch, Syn table and loop with loosely coupled tables

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;

Not applicable

Intervalmatch, Syn table and loop with loosely coupled tables

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

Not applicable

Intervalmatch, Syn table and loop with loosely coupled tables

Hi,

Try this

inner join (Product)

Intervalmatch(OrderDate) load StartDate, EndDate resident Product;

Drop Table Orders;

Not applicable

Intervalmatch, Syn table and loop with loosely coupled tables

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!

Community Browser