Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mbespartochnyy
Creator III
Creator III

Linking Slowly Changing Dimensions

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

1 Solution

Accepted Solutions
Anonymous
Not applicable

Hi

I think you are trying to achieve below result.

Capture1.PNG

Respective qvw attached below.

Regards

Bhuvan Agarwal

View solution in original post

3 Replies
Anonymous
Not applicable

Hi

I think you are trying to achieve below result.

Capture1.PNG

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
Creator III
Creator III
Author

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

Mikhail B.