Joining Two Tables

[kaygee28]

Good Day All, something that should be simple is really giving me a headache right now.

I am trying to join two tables with the one table consisting of 132 996 records while the other file has 188 031 records. I am using the table with the first number of records (132 996) as the primary table and left joining the second table onto it, however the number of records I am supposed to get is way higher than expected. Vlookup on excel reveals that only 110000 off 188031 records actually appear onto the first table from the second table.

Only a single column matches both tables and I expect the match to be made utilizing that particular column however when I pull the number of records on qlikview there are 179 425 results at the end while I was expecting 132 996 which is from the first table.

here is my code and attached is the table viewer image as well.

Directory;

LOAD APPStatus,

     Contract_Ref_no AS [Contr_Code],

     [Start_Day],

     [Start_Month],

     [Start Year],

     Status_cd,

     title_cd,

     Customer_name,

     race_type_cd As Race,

     Gender,

     Language_cd,

     Employ_status_cd,

     No_of_years,

     No_of_months,

     job_title,

     employer_name

FROM

[BMW Consolidated SF Data 10032016.xlsx]

(ooxml, embedded labels, table is [BMW (45)]);

Directory

Left Join [BMW (45)];

LOAD DealRef AS [Contr_Code],

     StartDate,

     NewUsed,

     AssetCode,

     AssetDesc,

     ChasNr,

     RegYear,

     ResAmt,

     AmtBorrow,

     EvenPay,

     DealLength,

     IDNum,

     F13,

     Age,

     Surname,

     Initials

FROM

[Demographica Portfolio Info V3 Data Tab.xlsx]

(ooxml, embedded labels, table is [Demographica Portfolio Info V3 ]);

I removed unnecessary fields in this cpode above to give you a clear indication of what I am trying to do.

I want to know why there is such a higher number of records when I was expecting only 132 996 records.

[sunny_talwar]

Do you happen to have duplicates in the fields you are joining on? Contr_Code? That would explain a number higher than original table

[Gysbert_Wassenaar]

For a record in your left table there could be multiple records in the secondary table. When you join the tables you get more records in the final table than you had in the original left table.

[its_anandrjs · Accepted solution]

Try to figure out which is the Master Table and Transaction table. Normally Master table has unique records or the transaction table has many line items.

If you join the table be careful about this.

1. Do the mapping if possible into transaction table.

2. Map with the master table with different tables (Masters) to the Fact table.

Regards

Anand

[kaygee28]

Hi guys thanks for your quick response yes there are duplicates since one person might have, multiple contracts with our client, so that's the issue cause I just created a quick table to see which Contr_Code appeared more than once in the second table.

So its a data problem once again, sorry guys I'll make sure I check my data prior to posting anymore questions I panicked prior to having my data checked.

Thanks everyone for your help!!!