topic Re: QlikView Script - activity between timestamps in QlikView

QlikView Script - activity between timestamps

mszuberl — Fri, 10 Feb 2017 19:18:15 GMT

Hi,

I would like to ask the experts here, what would the best approach be to solve this:

I have a file with about half a million records. Each task (record in the file) has two timestamps (task start, task end).

I would like to be able to tell, at any point in time, whether there was any activity (tasks in progress), how many unique tasks were in progress, total idle time (no tasks in progress), etc.

If you can point me to some reading material on how to do this, I will be very thankful!

Marcin

Re: QlikView Script - activity between timestamps

sdmech81 — Fri, 10 Feb 2017 19:37:21 GMT

Hi ,

Create one more filed where in you diduct task end time and task start time..Hope with this u can indetify the status..

Again to go specific ,u can put if condn to create tht extra columns something like:

If (taskend-taskstart>=so n so,'status1',if(follow other if condn to bring all status))

Hope helps..

Sachin

Re: QlikView Script - activity between timestamps

Peter_Cammaert — Fri, 10 Feb 2017 21:13:15 GMT

To get an idea about how to prepare for this kind of analysis, you should read the QV Desktop help article on the INTERVALMATCH script prefix. IIRC it includes an example with employee work start/stop times and manages to deduce stuff like "how many employees were at work at any time of day".

Re: QlikView Script - activity between timestamps

MarcoWedel — Fri, 10 Feb 2017 23:40:44 GMT

Hi,

maybe helpful:

Creating Reference Dates for Intervals

IntervalMatch

IntervalMatch and Slowly Changing Dimensions

Generating Missing Data In QlikView

regards

Marco

Re: QlikView Script - activity between timestamps

mszuberl — Mon, 13 Feb 2017 04:25:50 GMT

Marco,

Thank you for these very helpful links!

I like the Creating Reference Dates for Intervals approach very much, however, I am concerned with the number of records I would need to generate.

I typically deal with a datasets of around 250,000 records (policies in HIC's example; radiology equipment activity logs in my case), covering 12 months. Each record contains two timestamps (Scan Start Time and Scan End Time, along with scanner identifier and many other, non-critical fields).

These timestamps need to be analyzed using 1-minute intervals.

Using HIC's approach, I would need to generate 250,000 * 365 * 1440 records in the "Policies_x_ Dates" table. That's 131,400,000,000 records! I think this is beyond my memory and CPU limitations.

Is there another, less memory and CPU intensive, way to figure out which machines were on at any point in time, total idle time, total busy time, etc. (all based on master calendar selections)?

hic‌ rwunderlich‌ swuehl‌

Re: QlikView Script - activity between timestamps

mszuberl — Mon, 13 Feb 2017 23:20:58 GMT

Anyone?

Re: QlikView Script - activity between timestamps

MarcoWedel — Mon, 13 Feb 2017 23:44:32 GMT

maybe this helps:

Re: While loop for creating dates between 1985 and 2016 - performance issues

Subroutine to Create Data Model for From/To Date Selection

regards

Marco

Re: QlikView Script - activity between timestamps

us_enterprise_s — Fri, 24 Mar 2017 21:46:15 GMT

Thank you!