Intervalmatch with Multiple Tables and Slowly Changing Dimensions

Anonymous — Thu, 17 May 2018 19:37:45 GMT

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

topic Intervalmatch with Multiple Tables and Slowly Changing Dimensions in App Development

Intervalmatch with Multiple Tables and Slowly Changing Dimensions