Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I want to create a new field from my StartTime field called TimeBetweenTransactions
Below is an Example of what I need, what the best way to achieve this in the Script
StartTime | TimeBetweentransactions |
08:30:36 | 0 |
08:35:47 | 00:05:11 |
08:36:04 | 00:00:17 |
08:36:19 | 00:00:15 |
08:37:24 | 00:01:05 |
08:37:37 | 00:00:13 |
08:38:09 | 00:00:32 |
08:38:49 | 00:00:40 |
08:39:17 | 00:00:28 |
08:39:52 | 00:00:35 |
08:40:23 | 00:00:31 |
08:41:15 | 00:00:52 |
08:42:08 | 00:00:53 |
LOAD StartTime,
Interval(Alt(StartTime-Previous(StartTime),0)) as TimeBetweentransactions
FROM yoursource;
interval and Previous provided it is sorted correctly :
interval(StartTime - Previous(StartTime)) as TimeBetweentransactions
Update:
data:
LOAD * INLINE [
F1
08:30:36
08:35:47
08:36:04
08:36:19
08:37:24
08:37:37
08:38:09
08:38:49
08:39:17
08:39:52
08:40:23
08:41:15
08:42:08
];
test:
LOAD
F1,
Interval(F1 - Previous(F1)) as F1new
Resident data;
DROP Table data;
Hi Alan,
I would say you need to use the peek function to bring back the value from the prior row and then interval on this and the value from the current row, to evaluate the interval between them
Hope that helps
Joe
LOAD StartTime,
Interval(Alt(StartTime-Previous(StartTime),0)) as TimeBetweentransactions
FROM yoursource;
LOAD StartTime,
Interval(Alt(StartTime-Previous(StartTime),0)) as TimeBetweentransactions
FROM [http://community.qlik.com/thread/150947] (html, codepage is 1252, embedded labels, table is @1);
hope this helps
regards
Marco
Hi,
Both work as expected, however I have marked Marco as correct.
When I select the first Start Time it displays 00:00:00
but with Ioannis option I get no result
Thanks again guys
Regards
Alan
Hi Marco,
Why use ALT in the Expression
Regards
Alan
you're welcome
regards
Marco
As the first row does not have a Previous one, the term 'StartTime-Previous(StartTime)' delivers null, i.e. TimeBetweentransactions would also not get a zero value.
Alt() returns the first numerical value. The first row does not have a numerical value, so Alt(..., 0) returns zero instead.
hope this helps
regards
Marco
Excellent thanks,
very well explained
Regards
Alan