Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Can you provide the result table also which you are looking for?
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
Please see the attached file
hth
Sasi
You have provided data only for X1 and results for X1, X2, X3 and X4.
Please provide the data for X2, X3 and X4 bas well to provide the exact solution. Thank you.
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
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
Hello,
I have modified my post to provide the whole data.
Thanks a lot !
I have changed my post to fix this problem.
Thanks a lot for your participation