I have a query in Qlik that is running far too slow or won’t run at all. It’s a very large data set (millions of records). I’m trying to replicate what is happening in SQL with Qlik scripting in hopes that it will dramatically increase the load time and hopefully the amount of data I’m able to load. It seems the solution might be some combination of Extended Interval Match and Slowly Changing Dimensions, but I cannot figure out how to make this work.
I have roughly 6-8 tables with Begin Dates and End Dates. Each of these tables also has a TableID that needs to join along with the date to load the correct line of data.
Each of these tables links with the other tables through both an Id specific to the table (ie. VendorId, StoreId, ItemId, etc), but also must fall in a date range between the Transaction table Timestamp constraints.
How can I simplify this query by loading the individual tables in Qlik and let Qlik make the appropriate connections?
Here is an example of what my query is doing and what I’d like to accomplish within the Qlik app itself by separating the table loads.
SELECT *
FROM DataHouse.dbo.Transaction Transaction table
LEFT OUTER JOIN DataHouse.dbo.Item
ON Transaction.ItemId = Item.ItemId Item table
AND Item.BeginDate <= Transaction.Timestamp
AND (Item.EndDate >= Transaction.Timestamp)
AND Transaction.Timestamp between Item.BeginDate and Item.EndDate
LEFT OUTER JOIN DataHouse.dbo.IFA
ON Item.IFAId = IFA.IFAId
AND IFA.BeginDate <= Transaction.Timestamp
AND (IFA.EndDate >= Transaction.Timestamp)
AND Transaction.Timestamp between IFA.BeginDate and IFA.EndDate
LEFT OUTER JOIN DataHouse.dbo.Vendor
ON Item.VendorId = Vendor.VendorId
AND Vendor.BeginDate <= Transaction.Timestamp
AND (Vendor.EndDate >= Transaction.Timestamp)
AND Transaction.Timestamp between Vendor.BeginDate and Vendor.EndDate
LEFT OUTER JOIN DataHouse.dbo.Container
ON Transaction.ContainerId = Container.ContainerId
LEFT OUTER JOIN DataHouse.dbo.Store
ON Container.StoreId = Store.StoreId
AND Store.BeginDate <= Transaction.Timestamp
AND (Store.EndDate >= Transaction.Timestamp)
AND Transaction.Timestamp between Store.BeginDate and Store.EndDate
LEFT OUTER JOIN DataHouse.dbo.Shipment
ON Container.ShipmentId = Shipment.ShipmentId
LEFT OUTER JOIN DataHouse.dbo.[Event]
ON Item.EventId = [Event].EventId
AND [Event].BeginDate <= Transaction.Timestamp
AND ([Event].EndDate >= Transaction.Timestamp)
AND Transaction.Timestamp between [Event].BeginDate and [Event].EndDate
WHERE TRANSACTION.CLIENTID = 999
AND TRANSACTION.DAY > 20180101
AND TRANSACTION. DAY < 20180501
