Condition Formula

[Matthias1]

Hi everybody,

I want to calculate the Planned Trip Time of a bus being given

- the journeyID

- a continuous number (KEYrec)

- departure time  and arrival time

The idea is to take the arrival time from the last stop of this trip (The last stop is defined by the highest KEYrec for a given JourneyID) - the Departure time from the first stop (lowest KEYrec for the same JourneyID)

Thankful for any ideas on how to formulate that in Qlik syntax 🙂

[OmarBenSalem · Accepted solution]

It would be sthing like this (please adapt it to ur script)

t:
load * Inline [
JounreyID, Key, Dep,Arr
1,1,50,60
1,2,60,70
1,3,70,80
1,4,80,90
2,1,10,20
2,2,20,30
2,3,30,40
];

NoConcatenate
Time:
Load JounreyID,min(Key) as Min, min(Dep) as DepMin Resident t Group by JounreyID;
Left Join(Time)
Load JounreyID,max(Key) as max, max(Arr) as ArrMax Resident t Group by JounreyID;

left join(t)
load JounreyID, ArrMax-DepMin as PlannedTripTime Resident Time;
drop table Time;

Result:

[kaanerisen · Accepted solution]

Hi Mathias,

If you need to make it on front-end, you can use firstsortedvalue function to find the first departure and the last arrival time. So you can calculate total trip time.

Sample Script:

load * Inline [
JourneyID, Key, Dep,Arr
1,1,50,60
1,2,60,70
1,3,70,80
1,4,80,90
2,5,10,20
2,6,20,30
2,7,30,40
];

Dimension : JourneyID

Measure : 

FirstSortedValue(aggr(sum(Arr),JounreyID,Arr),-Key)-FirstSortedValue(aggr(sum(Dep),JounreyID,Dep),Key)

Hope it helps..