Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
gramqlik
New Contributor III

Make use of polling data timings & statuses

Hi there.

I have a simple extract QVW pulling data from a from a SQL table. The data comes from a system that checks the status of various facets of some machinery.

The data consists of 8 fields:

 

- Machine - e.g. ABC123. There are 10 different machines that are monitored.

- ATime - The date/time stamp in format dd/mm/yyy hh:mm:ss. The status of each machine is recorded every 10 seconds.

- 6 x Event fields (Event1, Event2 etc). These are Boolean (bit) fields in SQL, returning either 1 (True) or 0 (False).

 

At present, I'm just pulling this data into Qlikview as is, and displaying in a straight table. Obviously in Qlik, the bit fields display as -1 for True and 0 for False, so I've converted them to display text:

DUAL(IF(Event1, 'True', 'False'),Event1)

So I may have records as follows:

Machine ATimeEvent1Event2Event3etc.
ABC12327/06/2019 07:00:00FalseFalseTrue 
ABC12327/06/2019 06:59:50TrueFalseTrue 
ABC12327/06/2019 06:59:40TrueFalseFalse 
ABC12327/06/2019 06:59:30TrueFalseFalse 
ABC12327/06/2019 06:59:20TrueFalseFalse 
ABC12327/06/2019 06:59:10TrueFalseTrue 
ABC12327/06/2019 06:59:00FalseFalseTrue 
ABC12327/06/2019 06:58:50FalseFalseTrue 
ABC12327/06/2019 06:58:40FalseFalseFalse 
DEF45627/06/2019 07:00:00FalseFalseFalse 
DEF456etc.    

 

However, the data isn't hugely useful in this state. The source table has a couple of years' worth of data with records every 10 seconds, so it's not very illuminating to users.

What I need to do is:

1) Count the number of times each Event occurs in the displayed data, so in the above, Machine ABC123 had 1 instance of Event1 and 2 instances of Event3. I'd then be able to create a chart that showed the frequency of each event for each machine.

2) Identify how long in total each event lasted, so in the above, Machine ABC123 encountered Event1 for approx. 50 seconds (from it going into True state and reverting to False) and it encountered Event3 for approx. 30 seconds plus a second event that has not yet completed (but will last for a minimum of 20 seconds). I'd then be able to create a chart that displayed the total time a machine spent in True state for each Event in a given period. Perhaps this might be a stacked bar chart, so we could easily see the amount of time spent in True vs False state per event.

I realise timings will not be perfectly accurate - an event could begin at any time, not specifically at these polled 10-second intervals. However, it is close enough to allow us to flag problems and see trends, and the events could occur for much longer periods.

 

If I could get this information (points 1 and 2 above) to display in table(s), I wouldn't even need the original straight table full of True/False records.

 

I initially tried to do number 1) above by adding an extra column in the script:

IF ([Event1] = -1 AND Previous([Event1]) = 0, "ON",

IF ([Event1] = 0 AND Previous([Event1]) = -1, "OFF",

'')) AS Event1Status

But this didn't work as desired - I get weird results including a lot of "ON" values where Event1 = True (rather than just at the start of each event) and it looks like the Previous() function is doing something with the next column (Event2). I also played about with Peek() and Before(), but still couldn't get it to work. It's also very slow to reload and refresh the table due to the quantity of records & calculated field. I think I may be going down the wrong path with this approach, and something better could be done within the script.

 

So, any pointers on how I can achieve this?

 

Thanks.

1 Reply
Support
Support

Re: Make use of polling data timings & statuses

Best I can offer in this case is a link to the Design Blog area where you may be able to find something that will help get you in the direction you wish, sorry I could not narrow things down for you here, development is not my strong suit unfortunately, but hopefully someone else may now chime in, or you may find something in the Blog posts to help give you some ideas:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Oh, the other thing that may help is if you can attach a small sample app with the data model etc. and your chart with expressions etc., that generally is useful to the folks trying to help as well, as they can then see exactly what you are doing where etc. and ask the appropriate follow-up questions if need be.  

Regards,
Brett

To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.