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

[MK_QSL]

Can you provide the result table also which you are looking for?

[#3]

Hello Manish, what I want to obtain would be a table that would give me the duration I am trying to calculate for each pallet Ref.
So All is want is a table with 2 columns :

- Pallet Ref

-Duration of 1st Location

Thanks a lot

[sasiparupudi1]

Please see the attached file

hth

Sasi

[MK_QSL]

You have provided data only for X1 and results for X1, X2, X3 and X4.

[qlikviewwizard]

Hi Alexandre-Bernard

Please provide the data for X2, X3 and X4 bas well to provide the exact solution. Thank you.

[#7]

Hello Sasidhar,

unfortunately I can not open your file.
WOuld it be possible for you to put the script in a text file  please ?
Thanks a lot

Have a nice day

[sasiparupudi1]

LOAD

Time,Date ,Location,Ref,

timestamp#(Date&' '&Time,'DD/MM/YYYY hh:mm') as DateTime

Inline

[

Time,Date ,Location,Ref

14:00,15/12/14,A99,X1

14:00,15/12/14,A98,X1

15:00,16/12/14,A99,X1

18:00,16/12/14,A86,X1

18:00,16/12/14,A87,X1

16:00,15/12/14,A99,X2

15:00,16/12/14,A99,X2

16:00,16/12/14,A87,X2

];

in the chart object, use ref as dimension

in the expression for Duration use the following

interval(only(aggr(max(DateTime),Ref))-only(aggr(min(DateTime),Ref)),'h')

hth

Sasi

[#9]

Hello,
I have modified my post to provide the whole data.
Thanks a lot !

[#10]

I have changed my post to fix this problem.
Thanks  a lot for your participation