Hi,
I am quite new to Qlik Sense, but I have some interesting problem, that I can't solve and I need your help.
I have a field that every 0.n second save 1 or 0 in database. So my data looks like a very long stream of 1 and 0.
Inside qlik sense I can detect when status has changed from 0 to 1, like this:
Load
[id],
[time_stamp],
(If(status = '1' and Previous(status) = '0', 1, 0)) as Change;
My problem is, that there could be mild "errors", when status would change very quickly from 1 to 0 and back, like this:
[0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,0,0,1,0,0,1,1,1,1,0,0,0,0,0,0,0,0]. I would like to detect this as only one change. (from 0 to 1)
So, my instinct was to check if previous n values are all zero and current value is 1. I saw that there is ABOVE function, but only for chart analysis, not for script function (load function). How to handle this? I would need something like:
Load
[id],
[time_stamp],
(If(status = '1' and {sum of previous 20 status values} = '0', 1, 0)) as Change;
Even more, the second solution that I am looking for, and I would ask your help for, is to not rely on number of values, but rather on time stamp. So that function would be:
Load
[id],
[time_stamp],
(If(status = '1' and {sum of all status values from time_stamp to time_stamp-2s} = '0', 1, 0)) as Change;
Please help.
