Left join increases the nr of records by a potence

[#1]

Good afternoon,

I have a input table "PersonnelData" with many columns, including PersonnelNr and CompanyCode. I have texts to the company in an additional, two column table, and want to offer CompanyCode and Text in a single string, so I use

left join (PersonnelData)

load

CompanyCode

CompanyCode  & ' ' & Text as Company

Why does this left join then increase the number of records in PersonnelData by say 10 or 15?

Many thanks,

Leonardo

[swuehl]

Does your table with CompanyCode and Text have duplicate lines per CompanyCode?

[alexandros17]

because the table loaded doing

load

CompanyCode

CompanyCode  & ' ' & Text as Company

contains more than one record for each company, so any record of the first table is duplicated according the number of company contained in the second table.

[Peter_Cammaert]

What values do the extra rows have in field Company?

In the mean time, add a DISTINCT to the above left join and make sure that a single CompanyCode has only on Text value.

Peter

[Gabriel]

Hi,

Can you check if you have duplicate records after the left join for CompanyCode.

Which is possible.

[Colin-Albert]

I would consider using a Mapping Table and ApplyMap rather than a join. (Or multiple mapping tables!)

See this post by Henric Cronstrom hic for more details  Don't join - use Applymap instead

[anbu1984]

You should join on all the key fields else you will get duplicates. In the below example Keys are K1 and K2. But in second table second key is named as K12. Try run this script and you can see the dups. Then rename K12 to K2 and run the script and you will get correct result

T1:
LOAD * Inline [
K1,K2,Data
a,a,1
a,b,2
a,c,3 ];

Left Join(T1)
LOAD * Inline [
K1,K12,Data1
a,a,11
a,b,22
a,c,33 ];

[swuehl]

I would also recommend a MAPPING approach instead of a JOIN here.

And Peter, be careful when using DISTINCT on the JOIN table:

Left Join LOAD distinct remove rows!

[Peter_Cammaert]

Thanks Stefan. Of course you're right. I didn't know that behind the scenes, QV passes keywords as active properties to the two following tables. WAD, you love to hate it...

Apparently the solution is what I tend to do with preceding loads too: split the load and the left join and everything is e-ok.

Peter.