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: 
rustyfishbones
Master II
Master II

Create a new field between each Start Time Transaction

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

StartTimeTimeBetweentransactions
08:30:360
08:35:4700:05:11
08:36:0400:00:17
08:36:1900:00:15
08:37:2400:01:05
08:37:3700:00:13
08:38:0900:00:32
08:38:4900:00:40
08:39:1700:00:28
08:39:5200:00:35
08:40:2300:00:31
08:41:1500:00:52
08:42:0800:00:53

1 Solution

Accepted Solutions
MarcoWedel

LOAD StartTime,

           Interval(Alt(StartTime-Previous(StartTime),0)) as TimeBetweentransactions

FROM yoursource;

View solution in original post

9 Replies
giakoum
Partner - Master II
Partner - Master II

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;

Not applicable

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

MarcoWedel

LOAD StartTime,

           Interval(Alt(StartTime-Previous(StartTime),0)) as TimeBetweentransactions

FROM yoursource;

MarcoWedel

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

rustyfishbones
Master II
Master II
Author

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

rustyfishbones
Master II
Master II
Author

Hi Marco,

Why use ALT in the Expression

Regards

Alan

MarcoWedel

you're welcome

regards

Marco

MarcoWedel

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

rustyfishbones
Master II
Master II
Author

Excellent thanks,

very well explained

Regards

Alan