AnsweredAssumed Answered

Count Instances of State

Question asked by dcunningham on Jan 25, 2016
Latest reply on Jan 26, 2016 by TimCarmichael



I have a tag which reports either 0, 1, 2, or 3. Generally, only 0 or 1 will be reported, 2 and 3 are error codes. What I'd like to do (in DataLink) is count the number of instances of each value, but ignore consecutively repeated values. The data that I'm working with is at 5 second intervals, so if a certain state exists for a few minutes or even hours there would be a large number reported for the tally. I was using PrevVal('SWITCH.STATE','*')<>'SWITCH.STATE' as a filter in a calculated data query, but then I took a look at the values when the filter is applied to a compressed data query and realized that there's an issue. Here's an example of what my data looks like charted:



It's hard to tell, but each "red line" is actually made up of a number of discrete points at 5 second intervals.


And here's what I get from the compressed data query:



Number of Values:6
17-Jan-16 08:06:330
17-Jan-16 08:08:031
17-Jan-16 08:31:420
17-Jan-16 08:33:471
17-Jan-16 08:37:570
17-Jan-16 08:38:171


What I was expecting was: 1, 0, 1, 1, 0, 1, 0, 1, with the corresponding time stamps at the beginning of each instance of repeated values. The repeated 1s come in because there's a gap in the data (most likely because the unit was shut down and then turned on again). The resulting count would then be five 1s and three 0s. Replacing PrevVal with NextVal gets something closer to what I'm looking for:



Number of Values:6
17-Jan-16 08:06:281
17-Jan-16 08:07:580
17-Jan-16 08:31:371
17-Jan-16 08:33:420
17-Jan-16 08:37:521
17-Jan-16 08:38:120


The issues are that it misses the final 1 as well as missing the 1 after the gap.


Is there a way to get what I'm looking for with a filter and calculated data query?