Derive two new columns from existing one column

[nihhalmca]

Hi All -

How to derive two new columns from existing one column based on condition.

For instance:

Source:

Expected Result: Splitting "AirPort" column as two columns like 1. Departure Airport 2. Arrival Airport 

Thank you.

[MarcoWedel]

Based on which key do you assign the arrival to the corresponding departure?

[nihhalmca]

Thanks for response. Do not have key field. Data is in order like destination then origin and again destination and origin so on... have to go record by record. 

For instance: Line number 1 is destination, Line2 is Origin, Line3 is Destination, Line4 is Origin sequence is this way.

Thanks. 

[MarcoWedel]

then maybe one solution could be:

table1:
LOAD Div(RecNo()+1,2) as ID,
     * 
Inline [
Line, AirPort
Departure, MUM
Arrival, LON
Departure, NYK
Arrival, HYD
Departure, CHE
Arrival, DEN
];

table2:
Generic
LOAD ID,
     Line&' Airport',
     AirPort
Resident table1;

[nihhalmca]

Thanks Marco. If I add one more column to my source what should be the change in  "Code". 

Actually above code will assign same number (ID) for two records same way I need to assign sequence like below.

Updated source:

NOTE: Actually there are 15 more columns after these 3 columns (above) however these 3 columns and 2 columns (derived columns - Departure airport and Arrival airport) are my "composite key" to link with data model.

Thank you.

[MarcoWedel · Accepted solution]

maybe like this?

table1:
LOAD RecNo() as RecNo,
     * 
Inline [
Line, Route, AirPort
Departure, A, MUM
Departure, B, MUM
Departure, C, MUM
Arrival, A, LON
Arrival, B, LON
Arrival, C, LON
Departure, A, NYK
Departure, B, NYK
Departure, C, NYK
Arrival, A, HYD
Arrival, B, HYD
Arrival, C, HYD
Departure, A, CHE
Departure, B, CHE
Departure, C, CHE
Arrival, A, DEN
Arrival, B, DEN
Arrival, C, DEN
];

table2:
LOAD *,
     Div(RecNo()+1,2) as ID
Resident table1
Order By Route, RecNo;

DROP Table table1;

table3:
Generic
LOAD ID,
     Line&' Airport',
     AirPort
Resident table2;

[nihhalmca]

Thank you.