topic Re: left join not working in QlikView

left join not working

samuel_brierley — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: left join not working

marcus_malinow — Tue, 18 Jul 2017 09:27:17 GMT

Hi Sam,

Try changing this to an INNER JOIN

Marcus

Re: left join not working

marcus_malinow — Tue, 18 Jul 2017 09:28:26 GMT

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

Re: left join not working

techvarun — Tue, 18 Jul 2017 09:29:38 GMT

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

Re: left join not working

prma7799 — Tue, 18 Jul 2017 09:32:45 GMT

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

Re: left join not working

effinty2112 — Tue, 18 Jul 2017 09:36:03 GMT

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

Re: left join not working

shiveshsingh — Tue, 18 Jul 2017 09:48:53 GMT

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

Re: left join not working

samuel_brierley — Tue, 18 Jul 2017 10:04:00 GMT

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