Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Not applicable

Duration according to variation of a field

Hello everyone,

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

TimeDate LocationRef
14:0015/12/14A991X1
15:0016/12/14A991X1
18:0016/12/14A870X1
22:0016/12/14A992X2
00:3017/12/14A992X2
12:0017/12/14A780X2
15:0017/12/14A993X3
18:0017/12/14A993X3
8:0018/12/14A645X3
10:0018/12/14A994X4
15:0018/12/14A994X4
18:0019/12/14A785X4

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 :

RefDuration (Hours)
X128
X214
X317
X432

thank you very much
Have a nice day

Jeremie

Tags (2)
14 Replies
Highlighted
MVP
MVP

Re: Duration according to variation of a field

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

Highlighted
Not applicable

Re: Duration according to variation of a field

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

Highlighted
Honored Contributor III

Re: Duration according to variation of a field

Please see the attached file

hth

Sasi

Highlighted
MVP
MVP

Re: Duration according to variation of a field

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

Highlighted
Honored Contributor II

Re: Duration according to variation of a field

Hi Alexandre-Bernard

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

Highlighted
Not applicable

Re: Duration according to variation of a field

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

Highlighted
Honored Contributor III

Re: Duration according to variation of a field

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

Highlighted
Not applicable

Re: Duration according to variation of a field

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

Highlighted
Not applicable

Re: Duration according to variation of a field

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