Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have this load script:
BusNumber | FromStation | ToStation | Departure | Arrival | FromTransitStation | ToTransitStation | Month | TransitDeparture | TransitDeparture | TransitDeparture |
201 | A | B | 10:00 | 12:00 | - | - | Jan | - | - | - |
201 | A | C | 10:00 | 18:00 | A | B | Jan | 10:00 | 10:00 | 10:00 |
201 | A | C | 10:00 | 18:00 | A | B | Jan | 11:00 | 11:00 | 11:00 |
201 | B | C | 14:00 | 18:00 | - | - | Jan | - | - | - |
201 | A | B | 11:00 | 13:00 | - | - | Feb | - | - | - |
201 | A | C | 11:00 | 19:00 | A | B | Feb | 10:00 | 10:00 | 10:00 |
201 | A | C | 11:00 | 19:00 | A | B | Feb | 11:00 | 11:00 | 11:00 |
201 | B | C | 15:00 | 19:00 | - | - | Feb | - | - | -
|
BusNumber | FromStation | ToStation | Departure | Arrival | FromTransitStation | ToTransitStation | Month | TransitDeparture | TransitDeparture | TransitDeparture |
201 | A | B | 10:00 | 12:00 | - | - | Jan | - | - | - |
201 | A | C | 10:00 | 18:00 | A | B | Jan | 10:00 | 10:00 | 10:00 |
201 | B | C | 14:00 | 18:00 | - | - | Jan | - | - | - |
201 | A | B | 11:00 | 13:00 | - | - | Feb | - | - | - |
201 | A | C | 11:00 | 19:00 | A | B | Feb | 11:00 | 11:00 | 11:00 |
201 | B | C | 15:00 | 19:00 | - | - | Feb | - | - | - |
Apparently you don't want that the data is joined on only the Transit field, but also on the Departure field.
So try this:
TableA: LOAD * INLINE [ BusNumber, FromStation, ToStation, Month, Departure, Arrival, Transit 201, A, C, Jan, 10:00, 18:00, B 201, A, B, Jan, 10:00, 12:00, None 201, B, C, Jan, 14:00, 18:00, None 201, A, C, Feb, 11:00, 19:00, B 201, A, B, Feb, 11:00, 13:00, None 201, B, C, Feb, 15:00, 19:00, None ]; NewTableA: NoConcatenate LOAD * Resident TableA; Left Join (NewTableA) LOAD ToStation as Transit, FromStation as FromTransitStation, ToStation as ToTransitStation, Departure as TransitDeparture, Departure, Arrival as TransitArrival Resident TableA; Drop table TableA;
Apparently you don't want that the data is joined on only the Transit field, but also on the Departure field.
So try this:
TableA: LOAD * INLINE [ BusNumber, FromStation, ToStation, Month, Departure, Arrival, Transit 201, A, C, Jan, 10:00, 18:00, B 201, A, B, Jan, 10:00, 12:00, None 201, B, C, Jan, 14:00, 18:00, None 201, A, C, Feb, 11:00, 19:00, B 201, A, B, Feb, 11:00, 13:00, None 201, B, C, Feb, 15:00, 19:00, None ]; NewTableA: NoConcatenate LOAD * Resident TableA; Left Join (NewTableA) LOAD ToStation as Transit, FromStation as FromTransitStation, ToStation as ToTransitStation, Departure as TransitDeparture, Departure, Arrival as TransitArrival Resident TableA; Drop table TableA;