topic Re: Linking Slowly Changing Dimensions in QlikView

Linking Slowly Changing Dimensions

mbespartochnyy — Sat, 08 Apr 2017 20:23:39 GMT

Hello community,

I'm having trouble wrapping my head around IntervalMatch function. I read couple of posts about it, but I'm having trouble implying it to my scenario.

I have a database containing Catalog of items, holding information like product ID, description, price, price effective and expiration date, etc. Then I have a separate database containing record of Purchases. The Purchases database holds Product ID, Purchase date and purchase quantity.

Some Product IDs repeat in Catalog because they had one price and description at one period in time and a different price and description later on. In attached QVW, when Catalog is joined to Purchases table in the data model and I create a table box showing Product ID, Purchase Date, Description, and Purchase Price I get three lines for each date because this particular item had a price change three times in the past.

I believe IntervalMatch is an appropriate function to solve this, but I'm having trouble writing a script to get it to work. Can anyone please help me fix this? I'm trying to get QV to show one description per product ID based on purchase date. Is this possible without writing a While Loop?

Thanks,

Mikhail Bespartochnyy

Re: Linking Slowly Changing Dimensions

Anonymous — Sat, 08 Apr 2017 21:19:17 GMT

I think you are trying to achieve below result.

Respective qvw attached below.

Regards

Bhuvan Agarwal

Re: Linking Slowly Changing Dimensions

sunny_talwar — Sun, 09 Apr 2017 01:43:54 GMT

May be like attached?

Catalog:

LOAD [Product ID],

[Product Description],

[Available Quantity],

[Purchase Price],

[Effective Date],

[Expiration Date]

FROM

[Slowly Changing Dimensions.xlsx]

(ooxml, embedded labels, table is Catalog);

Purchases:

LOAD [Product ID],

[Purchase Date],

[Quantity Purchased]

FROM

[Slowly Changing Dimensions.xlsx]

(ooxml, embedded labels, table is Purchases);

Left Join (Catalog)

IntervalMatch([Purchase Date], [Product ID])

LOAD [Effective Date],

[Expiration Date],

[Product ID]

Resident Catalog;

Left Join (Catalog)

LOAD *

Resident Purchases;

DROP Table Purchases;

Re: Linking Slowly Changing Dimensions

mbespartochnyy — Fri, 24 Nov 2017 16:46:46 GMT

Thanks, Bhuvan! This is exactly what I was looking for.

Mikhail B.