Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

LOAD StartTime,

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

FROM yoursource;

View solution in original post

9 Replies
Highlighted
Partner
Partner

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;

Highlighted
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

Highlighted

LOAD StartTime,

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

FROM yoursource;

View solution in original post

Highlighted

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

Highlighted
Master II
Master II

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

Highlighted
Master II
Master II

Hi Marco,

Why use ALT in the Expression

Regards

Alan

Highlighted

you're welcome

regards

Marco

Highlighted

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

Highlighted
Master II
Master II

Excellent thanks,

very well explained

Regards

Alan