Generates new lines and new columns in a table

[annick]

Hello,

I have this load script:

TableA:
Load * inline
[
BusNumber, FromStation, ToStation, Departure, Arrival,  Transit, 
201,   A,     C,    10:00,  18:00,    B
201,  A,    B,    10:00,   12:00,    None
201,   B,     C,    14:00,   18:00,    None];

generating this table : 

BusNumber	FromStation	ToStation	Departure	Arrival	Transit
201	A	B	10:00	12:00	None
201	A	C	10:00	18:00	B
201	B	C	14:00	18:00	None

I would like to add additional lines and columns, based on this logic:

- For each line that had a Transit non equal to None (i.e. Line 2 with Transit B)

•  I would like to retrieve the line in the table that has the ToStation = Transit of my current line (i.e. B) and FromStation = FromStation of my current line (i.e. A) ==> Line 1 . The FromStation, ToStation, departure and arrival of this matching line should be copied in 4 new columns of my current line as FromTransitStation (A), ToTransitStation (B), transit_departure (10:00) and transit_arrival (12:00)
• I would like to retrieve the line in the table that has the FromStation = Transit of my current line (i.e. B) and ToStation = ToStation of my current line (i.e. C) ==>. Line 3. I would like to create a new line that is a duplicate of my current line with: The FromStation, ToStation, departure and arrival of this matching line copied in 4 new columns of my current line as FromTransitStation (B), ToTransitStation (C), transit_departure (14:00) and transit_arrival (18:00)

The end result table: 

Bus Number	FromStation	ToStation	Departure	Arrival	Transit	FromTransit Station	ToTransit Station	Transit Departure	Transit Arrival
201	A	B	10:00	12:00	None
201	A	C	10:00	18:00	B	A	B	10:00	12:00	Updated line, new columns retrieved from Line 1
201	A	C	10:00	18:00	B	B	C	14:00	18:00	New line, new columns retrieved from Line 5
201	B	C	14:00	18:00	None

 Many thanks in advance for your help,

Annick

[sunny_talwar · Accepted solution]

One way to do this

TableA:
LOAD * INLINE [
    BusNumber, FromStation, ToStation, Departure, Arrival, Transit
    201, A, C, 10:00, 18:00, B
    201, A, B, 10:00, 12:00, None
    201, B, C, 14:00, 18:00, None
];

FinalTable:
NoConcatenate
LOAD *
Resident TableA
Where Transit = 'None';

TempTable:
NoConcatenate
LOAD *
Resident TableA
Where Transit <> 'None';

Left Join (TempTable)
LOAD ToStation as Transit,
	 FromStation as FromTransitStation,
	 ToStation as ToTransitStation,
	 Departure as TransitDeparture,
	 Arrival as TransitArrival
Resident TableA;

Concatenate (FinalTable)
LOAD *
Resident TempTable;

DROP Table TempTable;

TempTable:
NoConcatenate
LOAD *
Resident TableA
Where Transit <> 'None';

Left Join (TempTable)
LOAD FromStation as Transit,
	 FromStation as FromTransitStation,
	 ToStation as ToTransitStation,
	 Departure as TransitDeparture,
	 Arrival as TransitArrival
Resident TableA;

Concatenate (FinalTable)
LOAD *
Resident TempTable;

DROP Table TempTable;

DROP Table TableA;

[annick]

Thanks a lot , it works perfectly !