topic Timestamp; difference between rows in QlikView

Timestamp; difference between rows

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Timestamp; difference between rows

m_woolf — Mon, 10 Jul 2017 13:45:47 GMT

Load

Created,

Action,

[TU Name],

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

from ....

order by [TU Name], Created;

Re: Timestamp; difference between rows

Anonymous — Tue, 11 Jul 2017 07:33:50 GMT

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]);

Re: Timestamp; difference between rows

Kushal_Chawda — Tue, 11 Jul 2017 09:19:56 GMT

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;

Re: Timestamp; difference between rows

Anonymous — Tue, 11 Jul 2017 11:11:51 GMT

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;