Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Datediff - Different Columns - Across Rows

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.

RowNumberProduct IDTime Move StartTime Move EndTime in Location
1EB123401-01-2018 00:0001-01-2018 00:10-
2EB123402-01-2018 03:0002-01-2018 03:05[02-01-2018 03:00] - [01-01-2018 00:10] = 1 Day
3EB123404-01-2018 12:5504-01-2018 13:00[04-01-2018 12:55] - [02-01-2018 03:05] = 2 Days
4AB98704-01-2018 12:5504-01-2018 13:00-
5AB98705-01-2018 14:0005-01-2018 14:05[05-01-2018 14:00] - [04-01-2018 13:00] = 1 Day
6EB123408-01-2018 01:0008-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!

Tags (1)
8 Replies
Highlighted
Master III
Master III

Re: Datediff - Different Columns - Across Ros

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

Highlighted

Re: Datediff - Different Columns - Across Ros

Hi,

use interval()

try like

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

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted
Contributor
Contributor

Re: Datediff - Different Columns - Across Rows

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

Highlighted
Master III
Master III

Re: Datediff - Different Columns - Across Ros

I have updated the expression

Highlighted
Contributor
Contributor

Re: Datediff - Different Columns - Across Rows

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

Highlighted
Contributor
Contributor

Re: Datediff - Different Columns - Across Rows

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

Highlighted
Master III
Master III

Re: Datediff - Different Columns - Across Ros

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

Highlighted

Re: Datediff - Different Columns - Across Rows

Hi,

maybe one solution could be:

QlikCommunity_Thread_297347_Pic1.JPG

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