left join not working

[samuel_brierley]

hi all,

I imagine this is one of those times when the answer is blaringly obvious I just cant see it.

I'm trying to perform a left join as per below however the result contains all fields. table 1 contains 3000 records, table 2 contains 185,000 records. the resulting table (I thought) should contain 3000 records with the new field, in this case Siteid.

the result however is that the resulting table contains 77,000 records and I haven't the foggiest why.

NOx_All:
LOAD Time,
     id,
     specificLane,
     %SpeedFlow,
     Speed_StDev,
     Speed,
     Flow,
     [Vehicle Type],
     [Euro Standard],
     Vehicle_id,
     NOx
FROM
[XXX]
(qvd);

exit SCRIPT

   left join  join (NOx_All)
LOAD
     id,
     Siteid
FROM
[XXX]
(qvd);

Message was edited by: sam brierley

[marcus_malinow]

Hi Sam,

Try changing this to an INNER JOIN

Marcus

[marcus_malinow]

Although you still may end up with more records, as I guess you may have multiple records per id in your first table...

[techvarun]

Try Inner Join or try to load unique records in the second table

[prma7799]

Try like this

NOx_All:
LOAD Time,
     id,
     specificLane,
     %SpeedFlow,
     Speed_StDev,
     Speed,
     Flow,
     [Vehicle Type],
     [Euro Standard],
     Vehicle_id,
     NOx
FROM
[XXX]
(qvd);

   left  join (NOx_All)
LOAD
     id,
     Siteid
FROM
[XXX]
(qvd);

[effinty2112]

Hi Sam,

In the second table are all the values of id unique?

After the join are you seeing more than one value of Siteid associated with id?

cheers

Andrew

[shiveshsingh]

Can you explain what result are you expecting? I mean logic ..common or something else?

[samuel_brierley]

Hi all,

yes the values are unique in the second table.

I believe the issues is arising from residual spaces at the end of the strings.

I will close shortly if the fix works.

thanks all