topic Re: Datediff - Different Columns - Across Ros in QlikView

Datediff - Different Columns - Across Rows

CHL — Fri, 06 Apr 2018 10:35:50 GMT

Hi All,

I hope you can help!

I have some data which looks like the below and I am trying to track how long a product is in a location for.

RowNumber	Product ID	Time Move Start	Time Move End	Time in Location
1	EB1234	01-01-2018 00:00	01-01-2018 00:10	-
2	EB1234	02-01-2018 03:00	02-01-2018 03:05	[02-01-2018 03:00] - [01-01-2018 00:10] = 1 Day
3	EB1234	04-01-2018 12:55	04-01-2018 13:00	[04-01-2018 12:55] - [02-01-2018 03:05] = 2 Days
4	AB987	04-01-2018 12:55	04-01-2018 13:00	-
5	AB987	05-01-2018 14:00	05-01-2018 14:05	[05-01-2018 14:00] - [04-01-2018 13:00] = 1 Day
6	EB1234	08-01-2018 01:00	08-01-2018 01:05	[08-01-2018 01:00] - [04-01-2018 13:00] = 4 Days

So basically the time the last move ended is the time it was completed into its location and the time the next move started is the time it ended up leaving that location so the time in between is the dwell at location.

Many Thanks!

Re: Datediff - Different Columns - Across Ros

sasiparupudi1 — Fri, 06 Apr 2018 10:37:51 GMT

Interval([Time Move Start]-Above([Time Move End]),'d')

Re: Datediff - Different Columns - Across Ros

PrashantSangle — Fri, 06 Apr 2018 10:37:56 GMT

Hi,

use interval()

try like

interval(time2-time1,'D hh:mm')

Regards,

Re: Datediff - Different Columns - Across Rows

CHL — Fri, 06 Apr 2018 10:40:19 GMT

This does the same row I think? I need to to look at the previous row and the current row.

Re: Datediff - Different Columns - Across Ros

sasiparupudi1 — Fri, 06 Apr 2018 10:43:14 GMT

I have updated the expression

Re: Datediff - Different Columns - Across Rows

CHL — Fri, 06 Apr 2018 10:44:52 GMT

As above: This does the same row I think? I need to to look at the previous row and the current row. Thank you

Re: Datediff - Different Columns - Across Rows

CHL — Fri, 06 Apr 2018 10:48:21 GMT

This looks promising! Does this take into account the Product ID?

Re: Datediff - Different Columns - Across Ros

sasiparupudi1 — Fri, 06 Apr 2018 11:07:54 GMT

Interval([Time Move Start]-Above(TOTAL [Time Move End]),'d')

Re: Datediff - Different Columns - Across Rows

MarcoWedel — Tue, 10 Apr 2018 20:28:41 GMT

Hi,

maybe one solution could be:

table1:
LOAD * FROM [https://community.qlik.com/thread/297347] (html, codepage is 1252, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 8))));

Join
LOAD RowNumber,
    If([Product ID]=Previous([Product ID]),Interval([Time Move Start]-Previous([Time Move End]),'d hh:mm')) as TimeInLocation,
    If([Product ID]=Previous([Product ID]),Floor([Time Move Start])-Floor(Previous([Time Move End]))) as DaysInLocation
Resident table1
Order By [Product ID], [Time Move Start];

hope this helps

regards

Marco