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 | Location | Pallet |
---|---|---|
14:00 | A99 | X1 |
15:00 | A99 | X1 |
18:00 | A87 | X1 |
I want to measure the time duration from line 1 to line 3. SO I want for each pallet, 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 ?
thank you very much
Have a nice day
Jeremie
??? screenshot
There is no screenshot.
If you order your table by pallet, location and timestamp; you can do something like this:
if(pallet = peek('pallet) and location<> peek('location'), timestamp - peek('timestamp')) as timediff;
How about this:
TData:
LOAD pallet,
location,
locTime // registered time in location
FROM ...;
TimeDiffs:
LOAD pallet,
location,
addTime
if(pallet = Previous(pallet), addTime - Previous(addTime), 0) As timeDiff
;
LOAD pallet,
location,
Min(locTime) As addTime // time first added
Resident TData
GROUP BY pallet,
locTime
ORDER BY pallet,
locTime
DROP TABLE TData;
Here it is ! My mistake
Here is the screenshot
Is it possible to calculate this metric without adding extra table to my data model ?
Looks like there are some great tips on this conversation! Mr. Bernard, there were a couple of duplicate posts so I took the liberty of removing those as this one had the most meaningful responses. Although this post is displaying as 'assumed answered'. Please take a minute to mark correct and helpful answers.
If you have a follow up or secondary question I recommend you start a new thread so that people can search, view and respond to each topic separately.
Great job helping each other here!
Hi,
another solution could be:
tabTestData:
LOAD *,
If(Rand()>0.8,'A'&(Floor(Rand()*10)+90),Peek('Location')) as Location;
LOAD 'X'&RecNo() as Pallet,
Time((IterNo()-1)/24,'hh:mm') as Time
AutoGenerate 10
While IterNo()<=24;
tabPalLoc:
LOAD Time as Start,
Pallet,
Location,
RowNo() as PosID
Resident tabTestData
Where Pallet&'/'&Location<>Previous(Pallet&'/'&Location)
Order By Pallet, Time;
Left Join (tabPalLoc)
LOAD Pallet,
Start,
If(Previous(Pallet)=Pallet,Interval(Previous(Start)-Start)) as Duration
Resident tabPalLoc
Order By Pallet, Start desc;
DROP Table tabTestData;
hope this helps
regards
Marco