topic Re: Create a new field between each Start Time Transaction in QlikView

Create a new field between each Start Time Transaction

rustyfishbones — Thu, 05 Feb 2015 16:45:06 GMT

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

Re: Create a new field between each Start Time Transaction

giakoum — Thu, 05 Feb 2015 16:48:13 GMT

interval and Previous provided it is sorted correctly :

interval(StartTime - Previous(StartTime)) as TimeBetweentransactions

Update:

data:

LOAD * INLINE [

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;

Re: Create a new field between each Start Time Transaction

Thu, 05 Feb 2015 16:49:49 GMT

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

Re: Create a new field between each Start Time Transaction

MarcoWedel — Thu, 05 Feb 2015 16:50:29 GMT

LOAD StartTime,

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

FROM yoursource;

Re: Create a new field between each Start Time Transaction

MarcoWedel — Thu, 05 Feb 2015 16:53:31 GMT

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

Re: Create a new field between each Start Time Transaction

rustyfishbones — Thu, 05 Feb 2015 16:57:06 GMT

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

Re: Create a new field between each Start Time Transaction

rustyfishbones — Thu, 05 Feb 2015 17:05:18 GMT

Hi Marco,

Why use ALT in the Expression

Regards

Alan

Re: Create a new field between each Start Time Transaction

MarcoWedel — Thu, 05 Feb 2015 17:05:21 GMT

you're welcome

regards

Marco

Re: Create a new field between each Start Time Transaction

MarcoWedel — Thu, 05 Feb 2015 17:09:39 GMT

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

Re: Create a new field between each Start Time Transaction

rustyfishbones — Thu, 05 Feb 2015 17:18:14 GMT

Excellent thanks,

very well explained

Regards

Alan