Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

time between two events

Hello,

I have two signals measured every second.

When Signal_1 is below a certain value Signal_2 should/will change; and i need to measure the latency between the two.

In the table below at 11:42:13 Signal_1 is dropping below (or equal) to 49.9 and Signal_2 is following 7 second later (at 11:42:20). Signal 2 can drop to 0 or to a lower value (and i should measure the difference absolute and percentage).

Later, at 11:42:33 Signal_1 is going up above 49.8 and Signal 2 is going up again, 22 seconds later at 11:42:55

I should also monitor if Signal 2 is moving erratically without Signal_1 to be changing around the 49.8 value.

        

TimeSignal_1Signal_2Above 49.8below 49.8Latency
11:42:0450.010.911:42:04-
11:42:0550.010.911:42:05-
11:42:0850.010.911:42:08-
11:42:1050.010.811:42:10-
(A1)11:42:1349.810.8-11:42:137
11:42:1449.810.8-11:42:14
11:42:1549.810.8-11:42:15
11:42:1849.810.8-11:42:18
(A2)11:42:2049.80.0-11:42:20
11:42:2249.80.0-11:42:22
11:42:2449.80.0-11:42:24
11:42:2649.80.0-11:42:26
11:42:2849.80.0-11:42:28
11:42:3049.80.0-11:42:30
11:42:3249.80.0-11:42:32
(B1)11:42:3349.90.011:42:33-22
11:42:3449.90.011:42:34-
11:42:3649.90.011:42:36-
11:42:3949.90.011:42:39-
11:42:4049.90.011:42:40-
11:42:4249.90.011:42:42-
11:42:4349.90.011:42:43-
11:42:4649.90.011:42:46-
11:42:4949.90.011:42:49-
11:42:5249.90.011:42:52-
(B2)11:42:5549.94.711:42:55-
11:42:5649.94.711:42:56-
11:42:5949.94.711:42:59-
11:43:0249.94.711:43:02-
11:43:0349.94.711:43:03-
11:43:0549.95.211:43:05-
11:43:0849.95.211:43:08-
11:43:1049.95.211:43:10-

thank you,

Alex

1 Solution

Accepted Solutions
sunny_talwar

There might be a better way to do this... but this is what I have for you....

Table:

LOAD RowNo() as Key,

*,

If(Flag = Previous(Flag), Peek('NewFlag'), RangeSum(Peek('NewFlag'), 1)) as NewFlag;

LOAD *,

If(Signal_1 > 49.8, Time) as [Above 49.8],

If(Signal_1 <= 49.8, Time) as [Below 49.8],

If(Signal_1 > 49.8, 1, 2) as Flag;

LOAD * INLINE [

    Time, Signal_1, Signal_2

    11:42:04, 50.0, 10.9

    11:42:05, 50.0, 10.9

    11:42:08, 50.0, 10.9

    11:42:10, 50.0, 10.8

    11:42:13, 49.8, 10.8

    11:42:14, 49.8, 10.8

    11:42:15, 49.8, 10.8

    11:42:18, 49.8, 10.8

    11:42:20, 49.8, 0.0

    11:42:22, 49.8, 0.0

    11:42:24, 49.8, 0.0

    11:42:26, 49.8, 0.0

    11:42:28, 49.8, 0.0

    11:42:30, 49.8, 0.0

    11:42:32, 49.8, 0.0

    11:42:33, 49.9, 0.0

    11:42:34, 49.9, 0.0

    11:42:36, 49.9, 0.0

    11:42:39, 49.9, 0.0

    11:42:40, 49.9, 0.0

    11:42:42, 49.9, 0.0

    11:42:43, 49.9, 0.0

    11:42:46, 49.9, 0.0

    11:42:49, 49.9, 0.0

    11:42:52, 49.9, 0.0

    11:42:55, 49.9, 4.7

    11:42:56, 49.9, 4.7

    11:42:59, 49.9, 4.7

    11:43:02, 49.9, 4.7

    11:43:03, 49.9, 4.7

    11:43:05, 49.9, 5.2

    11:43:08, 49.9, 5.2

    11:43:10, 49.9, 5.2

];

FinalTable:

LOAD *,

If(Previous(TempLatency) > 0, Null(), Latency) as Latency_Final;

LOAD *,

Interval(If(NewFlag > 1, If(Signal_2 <> Previous(Signal_2), Time - Peek('New_Time'))), 's') as Latency,

If(Len(Trim(If(NewFlag > 1, If(Signal_2 <> Previous(Signal_2), Time - Peek('New_Time'))))) = 0 and Flag = Previous(Flag), Peek('TempLatency'), If(NewFlag > 1, If(Signal_2 <> Previous(Signal_2), Time - Peek('New_Time')))) as TempLatency;

LOAD *,

If(Flag <> Previous(Flag) or Signal_2 <> Previous(Signal_2), Time, Peek('New_Time')) as New_Time

Resident Table;

DROP Fields New_Time, TempLatency, Latency, NewFlag, Flag;

RENAME Field Latency_Final to Latency;

DROP Table Table;

Capture.PNG

View solution in original post

8 Replies
sunny_talwar

May be this

Table:

LOAD RowNo() as Key,

*,

If(Signal_1 > 49.8, Time) as [Above 49.8],

If(Signal_1 <= 49.8, Time) as [Below 49.8],

If(Signal_1 > 49.8, 1, 2) as Flag;

LOAD * INLINE [

    Time, Signal_1, Signal_2

    11:42:04, 50.0, 10.9

    11:42:05, 50.0, 10.9

    11:42:08, 50.0, 10.9

    11:42:10, 50.0, 10.8

    11:42:13, 49.8, 10.8

    11:42:14, 49.8, 10.8

    11:42:15, 49.8, 10.8

    11:42:18, 49.8, 10.8

    11:42:20, 49.8, 0.0

    11:42:22, 49.8, 0.0

    11:42:24, 49.8, 0.0

    11:42:26, 49.8, 0.0

    11:42:28, 49.8, 0.0

    11:42:30, 49.8, 0.0

    11:42:32, 49.8, 0.0

    11:42:33, 49.9, 0.0

    11:42:34, 49.9, 0.0

    11:42:36, 49.9, 0.0

    11:42:39, 49.9, 0.0

    11:42:40, 49.9, 0.0

    11:42:42, 49.9, 0.0

    11:42:43, 49.9, 0.0

    11:42:46, 49.9, 0.0

    11:42:49, 49.9, 0.0

    11:42:52, 49.9, 0.0

    11:42:55, 49.9, 4.7

    11:42:56, 49.9, 4.7

    11:42:59, 49.9, 4.7

    11:43:02, 49.9, 4.7

    11:43:03, 49.9, 4.7

    11:43:05, 49.9, 5.2

    11:43:08, 49.9, 5.2

    11:43:10, 49.9, 5.2

];

FinalTable:

LOAD *,

Interval(If(Signal_2 <> Previous(Signal_2), Time - New_Time), 's') as Latency;

LOAD *,

If(Flag <> Previous(Flag), Time, Peek('New_Time')) as New_Time

Resident Table;

DROP Table Table;

Capture.PNG

Not applicable
Author

Hi Sunny,

Thank you very much for help!

I am afraid your script worked partially.

- GOOD: the 7 second measured when the Signal_2 is decreasing and the 22 seconds when Signal_2 is going up again after Signal_1 is above 49.8 - this is working great!


- NOT SO GOOD: i don't know why the 6 seconds latency appears at 11:42:10... what is that measuring? Signal_1 is stable above 49.8 so the latency should be measured. Sames at second 11:43:05 when i get 32 seconds latency and i do not need it... having this "unneeded" latency measurements will jeopardize the quality of statistics.



Can you please adjust it to eliminate those useless KPI's?



Thank you,

Alex

sunny_talwar

- NOT SO GOOD: i don't know why the 6 seconds latency appears at 11:42:10... what is that measuring? Signal_1 is stable above 49.8 so the latency should be measured. Sames at second 11:43:05 when i get 32 seconds latency and i do not need it... having this "unneeded" latency measurements will jeopardize the quality of statistics.

I don't know what it is measuring, I am not a business expert my friend... I just tried to implement the logic I understood from your post... There was a slight error, I have fixed that in the code (seeing 10 instead of 32)

Capture.PNG

But in the above two scenarios, Signal_2 changed and that is why I thought you needed to see latency? Is that not what you want?

Table:

LOAD RowNo() as Key,

*,

If(Signal_1 > 49.8, Time) as [Above 49.8],

If(Signal_1 <= 49.8, Time) as [Below 49.8],

If(Signal_1 > 49.8, 1, 2) as Flag;

LOAD * INLINE [

    Time, Signal_1, Signal_2

    11:42:04, 50.0, 10.9

    11:42:05, 50.0, 10.9

    11:42:08, 50.0, 10.9

    11:42:10, 50.0, 10.8

    11:42:13, 49.8, 10.8

    11:42:14, 49.8, 10.8

    11:42:15, 49.8, 10.8

    11:42:18, 49.8, 10.8

    11:42:20, 49.8, 0.0

    11:42:22, 49.8, 0.0

    11:42:24, 49.8, 0.0

    11:42:26, 49.8, 0.0

    11:42:28, 49.8, 0.0

    11:42:30, 49.8, 0.0

    11:42:32, 49.8, 0.0

    11:42:33, 49.9, 0.0

    11:42:34, 49.9, 0.0

    11:42:36, 49.9, 0.0

    11:42:39, 49.9, 0.0

    11:42:40, 49.9, 0.0

    11:42:42, 49.9, 0.0

    11:42:43, 49.9, 0.0

    11:42:46, 49.9, 0.0

    11:42:49, 49.9, 0.0

    11:42:52, 49.9, 0.0

    11:42:55, 49.9, 4.7

    11:42:56, 49.9, 4.7

    11:42:59, 49.9, 4.7

    11:43:02, 49.9, 4.7

    11:43:03, 49.9, 4.7

    11:43:05, 49.9, 5.2

    11:43:08, 49.9, 5.2

    11:43:10, 49.9, 5.2

];

FinalTable:

LOAD *,

Interval(If(Signal_2 <> Previous(Signal_2), Time - Peek('New_Time')), 's') as Latency;

LOAD *,

If(Flag <> Previous(Flag) or Signal_2 <> Previous(Signal_2), Time, Peek('New_Time')) as New_Time

Resident Table;

DROP Table Table;

Can you explain the business logic so that I can fix this for you.

Best,

Sunny

Not applicable
Author

Hi Sunny,

The business logic is the following:

1) As time as Signal_1 is above 49.8 everything is fine and Signal_2 can be at any level... i do not measure the speed of change in Signal_2 (if is changing)

2) When Signal_1 is going down below 49.8 I need to measure how fast Signal_2 is decreasing or even stopping (ideal one counter for decreasing at least X% with X a variable in my Qlik file)

3) When Signal_2 is going up above 49.8 I need to measure how fast Signal_2 is going up (no of seconds for S2 to recover once S1 is back above 49.8)

4) Signal_2 should never grow if Signal_1 is below 49.8 (ideally should be zero)

Best,

Alex

sunny_talwar

In other words, are you saying that you would want to see the first change in Signal_2 after Signal_1 has changed? Is this what you are trying to get?

Not applicable
Author

Yes! This is my number 1 priority:

1. What is the latency of S2 after S1 dropped below 49.8

2. What is the latency of S2 after S1 come back above 49.8

Secondly I would like to monitor if Signal_2 is growing even if signal_1 is below 49.8 (which shoudn't happen)

sunny_talwar

There might be a better way to do this... but this is what I have for you....

Table:

LOAD RowNo() as Key,

*,

If(Flag = Previous(Flag), Peek('NewFlag'), RangeSum(Peek('NewFlag'), 1)) as NewFlag;

LOAD *,

If(Signal_1 > 49.8, Time) as [Above 49.8],

If(Signal_1 <= 49.8, Time) as [Below 49.8],

If(Signal_1 > 49.8, 1, 2) as Flag;

LOAD * INLINE [

    Time, Signal_1, Signal_2

    11:42:04, 50.0, 10.9

    11:42:05, 50.0, 10.9

    11:42:08, 50.0, 10.9

    11:42:10, 50.0, 10.8

    11:42:13, 49.8, 10.8

    11:42:14, 49.8, 10.8

    11:42:15, 49.8, 10.8

    11:42:18, 49.8, 10.8

    11:42:20, 49.8, 0.0

    11:42:22, 49.8, 0.0

    11:42:24, 49.8, 0.0

    11:42:26, 49.8, 0.0

    11:42:28, 49.8, 0.0

    11:42:30, 49.8, 0.0

    11:42:32, 49.8, 0.0

    11:42:33, 49.9, 0.0

    11:42:34, 49.9, 0.0

    11:42:36, 49.9, 0.0

    11:42:39, 49.9, 0.0

    11:42:40, 49.9, 0.0

    11:42:42, 49.9, 0.0

    11:42:43, 49.9, 0.0

    11:42:46, 49.9, 0.0

    11:42:49, 49.9, 0.0

    11:42:52, 49.9, 0.0

    11:42:55, 49.9, 4.7

    11:42:56, 49.9, 4.7

    11:42:59, 49.9, 4.7

    11:43:02, 49.9, 4.7

    11:43:03, 49.9, 4.7

    11:43:05, 49.9, 5.2

    11:43:08, 49.9, 5.2

    11:43:10, 49.9, 5.2

];

FinalTable:

LOAD *,

If(Previous(TempLatency) > 0, Null(), Latency) as Latency_Final;

LOAD *,

Interval(If(NewFlag > 1, If(Signal_2 <> Previous(Signal_2), Time - Peek('New_Time'))), 's') as Latency,

If(Len(Trim(If(NewFlag > 1, If(Signal_2 <> Previous(Signal_2), Time - Peek('New_Time'))))) = 0 and Flag = Previous(Flag), Peek('TempLatency'), If(NewFlag > 1, If(Signal_2 <> Previous(Signal_2), Time - Peek('New_Time')))) as TempLatency;

LOAD *,

If(Flag <> Previous(Flag) or Signal_2 <> Previous(Signal_2), Time, Peek('New_Time')) as New_Time

Resident Table;

DROP Fields New_Time, TempLatency, Latency, NewFlag, Flag;

RENAME Field Latency_Final to Latency;

DROP Table Table;

Capture.PNG

Not applicable
Author

Thank you Sunny!