Duration according to variation of a field

[#1]

Hello everyone,

I am facing a issue as I want to measure a duration in a table. Here is what the table looks like :

Time	Date	Location	Ref
14:00	15/12/14	A991	X1
15:00	16/12/14	A991	X1
18:00	16/12/14	A870	X1
22:00	16/12/14	A992	X2
00:30	17/12/14	A992	X2
12:00	17/12/14	A780	X2
15:00	17/12/14	A993	X3
18:00	17/12/14	A993	X3
8:00	18/12/14	A645	X3
10:00	18/12/14	A994	X4
15:00	18/12/14	A994	X4
18:00	19/12/14	A785	X4

I want to measure the time duration from line 1 to line 3. SO I want for each Reference, to calculate the interval of  time from 1st time it is registered until the location changes.

I dont know if there are any way of doing that using set analysis in my interval function ?

I would like to eventually obtain this table :

Ref	Duration (Hours)
X1	28
X2	14
X3	17
X4	32

thank you very much
Have a nice day

Jeremie

[Gysbert_Wassenaar]

For the data set you posted this will work:

Dimension: Ref

Expression: interval(max(Date)-min(Date)+max(Time)-min(Time),'h')

[#3]

Hello Gysbert,

Thx a lot for helping me.

I have only provided a sample of my table and for some references the max time does not match the time when the location changes.

Do you know any formula that would take the location into account in the calculation and that would measure the duration between the min(time) and the first time the location changes per pallet ?

[Gysbert_Wassenaar]

See attached example.

[#5]

I dont have access to your Qview document unfortunately..

Would you be able to send the script in a text file ?

[Gysbert_Wassenaar]

Here's the script:

Temp:
LOAD 
  time#(Time,'h:mm') as Time, 
  Date#(Date,'DD/MM/YYYY') as Date,
  Location, 
  Ref 
INLINE [
    Time, Date, Location, Ref
    14:00, 15/12/14, A991, X1
    15:00, 16/12/14, A991, X1
    18:00, 16/12/14, A870, X1
    22:00, 16/12/14, A992, X2
    00:30, 17/12/14, A992, X2
    12:00, 17/12/14, A780, X2
    15:00, 17/12/14, A993, X3
    18:00, 17/12/14, A993, X3
    8:00, 18/12/14, A645, X3
    10:00, 18/12/14, A994, X4
    15:00, 18/12/14, A994, X4
    18:00, 19/12/14, A785, X4
];

Result:
LOAD *, If(Location<>previous(Location) and Ref=previous(Ref),1,0) as Flag
Resident Temp
Order by Ref, Date, Time;

Drop Table Temp;

You can use Ref as dimension and the expression Interval(min({<Flag={1}>}Date+Time)-min(Date+Time),'h')