Calcultate DUration according to field variation

[#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	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

[maxgro]

??? screenshot

[m_woolf]

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;

[jonathandienst]

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;

[#5]

Here it is ! My mistake

[#6]

Here is the screenshot

[#7]

Is it possible to calculate this metric without adding extra table to my data model ?

[Anonymous]

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!

[MarcoWedel]

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