Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
carlcimino
Creator II
Creator II

Calculating Outbound/Inbound Flight Times for Connections

Hello I am trying to calculate the flight duration for the outbound portion of the trip and the inbound portion of the trip.  A trip can have multiple segments on the oubound and inbound portions.  I've attached some sample data and in it there is a sequence number for each segment and also a field to show if a segment is a connection on the trip.   In the example below I am looking to calculate the leg duration (green column).  I know I have to use a combination of the Trip Key, Flight Sequence and Connection and then sum the Segment Duration but not sure how to set that up with set analysis.  How would one go about doing this?  Is this an aggr situation? Do I need to do any additional data modeling work to facilitate?  Any suggestions would be appreciated.

Trip KeyFlight SequenceDeparture AirportArrival AirportConnectionSegment DurationLeg Duration
20210622AXU78302838611BHMATLY1.2 
20210622AXU78302838612ATLCRW 1.32.5
20210622AXU78302838613CRWATLY1.5 
20210622AXU78302838614ATLBHM 0.92.4

 

Labels (1)
1 Solution

Accepted Solutions
Kushal_Chawda

@carlcimino  try this

 

Data:
LOAD [Trip Key], 
     [Flight Sequence], 
     [Departure Airport], 
     [Arrival Airport], 
     Connection, 
     [Segment Duration]
FROM
[Sample Trip Data.xlsx]
(ooxml, embedded labels, table is Data);


T1:
NoConcatenate
LOAD *,

     if([Flight Sequence]=1 and len(trim(Connection))=0,null(),
     if(lower(trim(Connection))='y',RangeSum(1,Peek('connection_group')),
     if(len(trim(Connection))=0,Peek('connection_group'),
     ))) as connection_group,
    
     if(lower(trim(Connection))='y',1,
     RangeSum(1,Peek('connection_sequence'))) as connection_sequence,
     
     if([Trip Key]<>Previous([Trip Key]) or ([Trip Key]=Previous([Trip Key]) and lower(trim(Connection))='y' and previous(lower(trim(Connection)))<>'y'), 
     [Segment Duration], RangeSum([Segment Duration],Peek('Segment_lag_temp')))  as Segment_lag_temp
Resident Data
Order by [Trip Key], [Flight Sequence];

Left Join(T1)
LOAD [Trip Key],
      connection_group,
      max(connection_sequence) as Max_connection_sequence
Resident T1
Group by [Trip Key],
      connection_group;

DROP Table Data;

Final:
LOAD *,
     if(Max_connection_sequence=connection_sequence and lower(trim(Connection))<>'y' ,Segment_lag_temp,
     if(len(trim(connection_group))=0,[Segment Duration],0)) as Segment_lag
Resident T1;

DROP Table T1;

DROP Fields Segment_lag_temp,Max_connection_sequence,connection_sequence,connection_group;

 

 

Kushal_Chawda_0-1625927975566.png

 

View solution in original post

6 Replies
rbartley
Specialist II
Specialist II

Hi,

It seems to me that you need a way of indicating that the leg is outbound or inbound, then you can simply add up the leg duration of each trip key-outbound/inbound combination 

Kushal_Chawda

@carlcimino  you might need to do scripting to do this

Data:
LOAD [Trip Key], 
     [Flight Sequence], 
     [Departure Airport], 
     [Arrival Airport], 
     Connection, 
     [Segment Duration]
FROM
[Sample Trip Data.xlsx]
(ooxml, embedded labels, table is Data);

T1:
LOAD *,
     if([Trip Key]<>Previous([Trip Key]) or ([Trip Key]=Previous([Trip Key]) and lower(trim(Connection))='y' and previous(lower(trim(Connection)))<>'y'), 
     [Segment Duration], RangeSum([Segment Duration],Peek('Segment_lag_temp')))  as Segment_lag_temp
Resident Data
Order by [Trip Key], [Flight Sequence];

DROP Table Data;

Final:
LOAD *,
     if(lower(trim(Connection))='y',0,Segment_lag_temp) as Segment_lag
Resident T1;

DROP Table T1;

DROP Field Segment_lag_temp;

 

Screenshot 2021-07-09 133740.png

carlcimino
Creator II
Creator II
Author

Hi @Kushal_Chawda This looks to be almost what I was expecting.  Where it does not look as expected is when there are no connections at all like the trip below which is just a there and back.   In this case the Segment_Lag should be equal to the Segment Duration.  But that would have to be an extra peek to see if there are any Connection = Y at all right?  Where would that go in the T1 section or the Final?  Or would you another step in between?  Thanks again I appreciate the help.

Trip KeyFlight SequenceDeparture AirportArrival AirportConnectionSegment Duration
20210621AXM11001188063911MTYMEX 1.6
20210621AXM11001188063912MEXMTY 1.8
Kushal_Chawda

@carlcimino  try this

 

Data:
LOAD [Trip Key], 
     [Flight Sequence], 
     [Departure Airport], 
     [Arrival Airport], 
     Connection, 
     [Segment Duration]
FROM
[Sample Trip Data.xlsx]
(ooxml, embedded labels, table is Data);


T1:
NoConcatenate
LOAD *,

     if([Flight Sequence]=1 and len(trim(Connection))=0,null(),
     if(lower(trim(Connection))='y',RangeSum(1,Peek('connection_group')),
     if(len(trim(Connection))=0,Peek('connection_group'),
     ))) as connection_group,
    
     if(lower(trim(Connection))='y',1,
     RangeSum(1,Peek('connection_sequence'))) as connection_sequence,
     
     if([Trip Key]<>Previous([Trip Key]) or ([Trip Key]=Previous([Trip Key]) and lower(trim(Connection))='y' and previous(lower(trim(Connection)))<>'y'), 
     [Segment Duration], RangeSum([Segment Duration],Peek('Segment_lag_temp')))  as Segment_lag_temp
Resident Data
Order by [Trip Key], [Flight Sequence];

Left Join(T1)
LOAD [Trip Key],
      connection_group,
      max(connection_sequence) as Max_connection_sequence
Resident T1
Group by [Trip Key],
      connection_group;

DROP Table Data;

Final:
LOAD *,
     if(Max_connection_sequence=connection_sequence and lower(trim(Connection))<>'y' ,Segment_lag_temp,
     if(len(trim(connection_group))=0,[Segment Duration],0)) as Segment_lag
Resident T1;

DROP Table T1;

DROP Fields Segment_lag_temp,Max_connection_sequence,connection_sequence,connection_group;

 

 

Kushal_Chawda_0-1625927975566.png

 

carlcimino
Creator II
Creator II
Author

@Kushal_Chawda  rock on.  this appears to work as I expected.  thank you very much for your help!

Kushal_Chawda

Glad that it worked