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

Calcultate DUration according to field variation

Hello everyone,

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

TimeLocationPallet
14:00A99X1
15:00A99X1
18:00A87X1

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

8 Replies
Highlighted
MVP
MVP

Re: Calculate a duration according to a variation of a Field

??? screenshot

Highlighted
Honored Contributor II

Re: Calculate a duration according to a variation of a Field

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;

Highlighted
MVP
MVP

Re: Calculate a duration according to a variation of a Field

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;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Not applicable

Re: Calculate a duration according to a variation of a Field

Here it is ! My mistake

Highlighted
Not applicable

Re: Calculate a duration according to a variation of a Field

Here is the screenshot

Highlighted
Not applicable

Re: x

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

Highlighted
Valued Contributor II

Re: Calcultate DUration according to field variation

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!

Highlighted

Re: Calcultate DUration according to field variation

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;

QlikCommunity_Thread_176458_Pic1.JPG

QlikCommunity_Thread_176458_Pic2.JPG

hope this helps

regards

Marco