Linking Slowly Changing Dimensions

[mbespartochnyy]

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

[Anonymous · Accepted solution]

Hi

I think you are trying to achieve below result.

Respective qvw attached below.

Regards

Bhuvan Agarwal

[sunny_talwar]

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;

[mbespartochnyy]

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

Mikhail B.