Timestamp; difference between rows

[Anonymous]

Hi

Have treid to follow the different threads but cannot get them to work 😞

In example below i have actions started and finished with commom TU name. I want to make a trend to show the actual time between start and finish for each TU.

Should i do this in excel before import or can i do it in Qlikview.

I only have persnal edition..

Created	Action	TU Name	Location
2017-07-10 10:21:22.120	FINISHED	599999990002694000	PG02WP45
2017-07-10 10:22:56.165	STARTED	599999990002694000	PG02WP45
2017-07-10 10:31:06.370	FINISHED	599999990002787000	PG02WP42
2017-07-10 10:33:52.375	STARTED	599999990002787000	PG02WP42
2017-07-10 09:32:30.543	FINISHED	599999990003059000	PG01WP43
2017-07-10 09:34:54.552	STARTED	599999990003059000	PG01WP43
2017-07-10 10:17:56.165	FINISHED	599999990003168000	PG03WP45

Thanks

/des

[m_woolf]

Load

     Created,

     Action,

     [TU Name],

     If([TU Name] = peek('TU Name'),Created-peek('Created')) as ElapsedTime

from ....

order by [TU Name], Created;

[Anonymous]

Hi

Tried that but did not get desired result. Just got errors when i tried to add the script as you suggested but here i got some results.

However the elapsed time for each event was over 23 hours.

Attached the file so you can see.

LOAD *,
Month(Date) As Month,
Year(Date) As Year,
Day(Date) As Day,
Hour(Created) As hour,
Minute(Created) As Min;
LOAD Date(Floor(Created)) as Date,
Created,
Action,
[TU Name],
Location,
[Super TU],
TargetList,
[Target Location],
[Transport Order],
[Transport order type],
[Transport Error Code],
If([TU Name] = peek('TU Name'),Created-peek('Created')) as ElapsedTime

FROM

(ooxml, embedded labels, table is [pallet in_1]);

[Kushal_Chawda]

You should have order by clause to work the logic correctly

Data:

LOAD Date(Floor(Created)) as Date,
Created,
Action,
[TU Name],
Location,
[Super TU],
TargetList,
[Target Location],
[Transport Order],
[Transport order type],
[Transport Error Code]
FROM

(ooxml, embedded labels, table is [pallet in_1]);

Final:

noconcatenate

LOAD *,

        If([TU Name] = peek('TU Name'),Created-peek('Created')) as ElapsedTime

Resident Data

order by [TU Name], Created;

drop table Data;

[Anonymous]

Hi

Get the following

Then

Copied in your suggestion

LOAD *,

      Month(Date) As Month,

      Year(Date) As Year,

      Day(Date) As Day,

      Hour(Created) As hour,

      Minute(Created) As Min;

LOAD Date(Floor(Created)) as Date,

Created,

Action,

,

Location,

,

TargetList,

,

,

,

FROM

(ooxml, embedded labels, table is );

Final:

noconcatenate

LOAD *,

        If( = peek('TU Name'),Created-peek('Created')) as ElapsedTime

Resident Data

order by , Created;

drop table Data;