0 Replies Latest reply: Sep 8, 2015 5:38 PM by Chris Kelley RSS

    Resolving Complex Data Corelations

    Chris Kelley

      I have a very interesting problem that I am trying to solve and am running into a few issues for which I am hoping to tap the broader community for some wisdom.

       

      Here is the core background:

       

      1. I have a Qlik Sense App that pulls data from a series of telemetry QVD files. These files are very large - runs about 200M rows of data, and it is highly structured. These telemetry QVD files contain measurement data, so think of it as time series in nature, and very granular. There is a timestamp and a duration for which the measure is valid in these QVDs. Lets say that I am tracking the charge associated with something here.

      Example:

       

      Timestamp               Charge               Computer

      1/1/11 00:00:00        $1.9999             happy123

      1/1/11 00:02:00        $2.9999             happy124

      1/1/11 00:04:00        $3.9999             happy125

      1/1/11 01:00:00        $1.9999             happy123

      1/1/11 01:02:00        $2.9999             happy124

      1/1/11 01:04:00        $3.9999             happy125

       

      (and so forth)

       

       

      1. I have another set of QVDs that store a type of event data - the columns here indicate the name of the event and the duration, so we calculate a start and end timestamp from this, and effectively have a "time window" associated with the row.

       

      Example:

       

      Timestamp               Duration            (Seconds)          Computer           User      OutputLOC

      1/1/11 00:00:00        600                               happy123           Jimmie  100

      1/1/11 00:04:00        300                               happy125           Jane      1000

       

      So, in this example, “Jimmie” has used computer “happy123” for 600 seconds, starting at 1/1/11 00:00:00, and produced 100 lines of code during this period.

       

       

      1. Both of the QVD sets have a key that is valid for each – “computer”. So, I have rows in the telemetry QVD and the event QVD showing "computer", and thus I can link the time series data and the event data to one common identifier. The App had no issues establishing this relationship automatically at load.

      Now here is the problem:

       

      1. So, using the above example, I know that Jimmie averaged 6 seconds per unit of output for the period starting 1/1/11 00:00:00. Now, how do I charge this output? In other words, how can I get Qlik Sense to report on the associated “Charge” from the telemetry QVD that applies for the period and output for this row for Jimmie?