Left Join duplicates records

[annick]

Hello,

I have this load script:

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,
  Arrival as TransitArrival
Resident TableA;

Drop table TableA;

which produces this table:

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	-	-	-

There are some extra lines duplicated by month which are not correct. When performing the join, I would like to have a match on the departure month, in order to obtain the following table:

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	-	-	-

Many thanks in advance for your help,

Annick

[Gysbert_Wassenaar · Accepted solution]

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;

[Vegar · Accepted solution]

It looks like you at not including month in your join. This is causing double"hits" for transit B

NewTableA:
NoConcatenate
LOAD *
Resident TableA;
Left Join (NewTableA)
LOAD ToStation as Transit,
Month as Month, //add this row
FromStation as FromTransitStation,
ToStation as ToTransitStation,
Departure as TransitDeparture,
Arrival as TransitArrival
Resident TableA;