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.
