Intervalmatch with Multiple tables and Slowly Changing Dimensions

Anonymous — Fri, 18 May 2018 17:01:09 GMT

I have a query that is pulling from a huge dataset and is taking far too long to load or times out completely. I would like to try using Qlik instead of a query to load and join/filter the individual tables to see if the performance might be improved.

I'm wanting to replicate in Qlik what my SQL query is doing with JOINS and a BETWEEN CLAUSE. I think the solution lies in Intervalmatch, Slowly Changing Dimensions, and Bridge tables, but haven’t had any success.

This is a simplified version. I have roughly 6-8 tables that join based on their individual table Id’s (ie. VendorId, ItemId, TransactionId, EventId, etc) and a Begin/End date relative to the Transaction Timestamp. The dates include dates and times.

I’ve included the SQL query and my simplified attempt in Qlik to join just the two largest tables (Transactions and Item tables) with Intervalmatch and a Bridge table. Any ideas?

here is a portion of the SQL query

FROM DataWH.dbo.Transactions

LEFT JOIN DataWH.dbo.Item

ON Transactions.ItemId = Item.ItemId

AND Item.BeginDate <= Transactions.[Timestamp]

AND (Item.EndDate >= Transactions.[Timestamp])

The above repeats for multiple tables. Vendor table, Event table, etc.

topic Intervalmatch with Multiple tables and Slowly Changing Dimensions in QlikView

Intervalmatch with Multiple tables and Slowly Changing Dimensions