0 Replies Latest reply: May 17, 2018 3:37 PM by Matt Brackins RSS

    Intervalmatch with Multiple Tables and Slowly Changing Dimensions

    Matt Brackins

      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