2 Replies Latest reply: Oct 2, 2015 10:53 AM by Henric Cronström RSS

    Intervalmatch Assistance

    Chris Kelley

      Hi community,

       

      I have a challenging case below on the use of Intervalmatch, and I am hoping the broad community here can assist.

       

      Intro:

       

      So, I have two key tables of data. These tables are as follows (the number of actual columns is much greater, but the important ones are below):

       

      TELEMETRY:

       

      Load

                  [Electrical Circuit Group],

                  [Energy Consumption, Meter, kWh],

                  [Data Timestamp]

      FROM telemetry.qvd]

      (qvd);

       

      BUSINESS_PROCESS:

       

      Load

       

      [Process Enter Timestamp],

      [Process Exit Timestamp],

      [Quantity of Widgets],

      [Widget Name],

                  [Electrical Circuit Group]

       

      FROM [business.qvd]

      (qvd);

       

       

      Background:

       

      By default, QlikSense will associate the two tables based on [Electrical Circuit Group], which is a good thing for us, as it allows us to link where the telemetry applies to, and for business process, where the process was run against.

       

      We can also confirm that when the data was acquired from the DB, processed, and then written into the QVD files above, the respective date fields were declared as timestamp fields.

       

      For instance:

      timestamp([timestamp]) as [Data Timestamp],

       

       

      Now, in addition to [Electrical Circuit Group], we have both a time window, and a time stamp. The time window is shown in BUSINESS_PROCESS as the following fields:

       

      [Process Enter Timestamp], [Process Exit Timestamp]

       

      We can also say that [Quantity of Widgets] is always a value of “1”, and thus, you can infer that between [Process Enter Timestamp] and [Process Exit Timestamp], exactly one unit of [Widget Name] was produced.

       

      Now, what we want to know, is for a given unit of [Widget Name], what is the sum([Energy Consumption, Meter, kWh]) result for the period between [Process Enter Timestamp] and [Process Exit Timestamp]? Or more simply, how much energy was consumed to produce said widget? Similarly, we can then run a report against [Widget Name] and other dimensions to understand the sum or average of all energy consumption for producing said widget name.

       

      You should also know that business.qvd is a growing data set of values, with no fewer then 500,000 records, increasing by roughly 10,000 records a day. telemetry.qvd is growing by 2M records per day, and is about 260,000,000 records. Hence, a join is very, very, very expensive. You can assume that the host report server is not an issue. You can also assume we really have many identically formatted QVD files and are not really reading 260,000,000 records from just one (because that, well, would be a wee bit silly).

       

      So, here is our latest (failed) attempt:

       

      TELEMETRY:

       

      LOAD *,

      [Electrical Circuit Group] as TmpSPID,

      [Energy Consumption, Meter, kWh],

      [Electrical Circuit Group] & '|' & [Data Timestamp] as [SPID+TransDate]

       

      FROM [business.qvd]

      (qvd);

       

       

      BUSINESS_PROCESS:

       

      LOAD

      [Process Enter Timestamp],

      [Process Exit Timestamp],

      [Quantity of Widgets],

      [Widget Name],

                  [Electrical Circuit Group],

                  [Electrical Circuit Group] & '|' & [Process Enter Timestamp] & '|' & [Process Exit Timestamp] as [SPID+Interval]

       

      FROM [business.qvd]

      (qvd);

       

       

      TmpBridgeTable:

       

      IntervalMatch ([Data Timestamp],TmpSPID)

      Load distinct [Process Enter Timestamp], [Process Exit Timestamp],[ Electrical Circuit Group] as TmpSPID

      Resident BUSINESS_PROCESS;

       

       

      BridgeTable:

       

      Load

      TmpSPID & '|' & [Data Timestamp] as [SPID+TransDate],

      TmpSPID & '|' & [Process Enter Timestamp] & '|' & [Process Exit Timestamp] as [SPID+Interval]

      Resident TmpBridgeTable;

      Drop Field TmpSPID;

      Drop table TmpBridgeTable;

       

       

      How this fails:

       

      When we run the above, we get a very clear loop. We could be going about this all wrong, or simply we may have to work in some joins, but this has three fairly experienced folks very stumped. We suspect that the association on [Electrical Circuit Group] is the problem here, but how do we ensure we can still associate by this field, without the loop (see below)?

       

      Untitled.png

        • Re: Intervalmatch Assistance
          Chris Kelley

          No takers? All advice is welcome.

            • Re: Intervalmatch Assistance
              Henric Cronström

              TELEMETRY:
              Load
              [Data Timestamp] & '|' & [Electrical Circuit Group] as DateKey,
              [Electrical Circuit Group],
              [Energy Consumption, Meter, kWh],
              [Data Timestamp]
              FROM telemetry.qvd] (qvd);

              BUSINESS_PROCESS:
              Load
              [Process Enter Timestamp] & '|' & [Process Exit Timestamp] & '|' & [Electrical Circuit Group] as IntervalKey,
              [Process Enter Timestamp],
              [Process Exit Timestamp],
              [Quantity of Widgets],
              [Widget Name],
              [Electrical Circuit Group]
              FROM [business.qvd] (qvd);

              tmpIntervalMatch:
              IntervalMatch ([Data Timestamp], [Electrical Circuit Group])
              Load
              [Process Enter Timestamp],
              [Process Exit Timestamp],
              [Electrical Circuit Group]
              FROM [business.qvd] (qvd);

              IntervalMatch:
              Load
              [Process Enter Timestamp] & '|' & [Process Exit Timestamp] & '|' & [Electrical Circuit Group] as IntervalKey,
              [Data Timestamp] & '|' & [Electrical Circuit Group] as DateKey
              Resident tmpIntervalMatch;

              Drop Table tmpIntervalMatch;
              Drop Field [Electrical Circuit Group] From TELEMETRY;

               

               

              HIC