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:
- 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)
- 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.
- 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:
- 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?
