topic Re: Joining Two Tables in QlikView

Joining Two Tables

kaygee28 — Tue, 15 Mar 2016 08:26:30 GMT

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

Re: Joining Two Tables

sunny_talwar — Tue, 15 Mar 2016 08:49:37 GMT

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

Re: Joining Two Tables

Gysbert_Wassenaar — Tue, 15 Mar 2016 08:52:24 GMT

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.

Re: Joining Two Tables

its_anandrjs — Tue, 15 Mar 2016 08:59:29 GMT

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

Re: Joining Two Tables

kaygee28 — Tue, 15 Mar 2016 09:26:16 GMT

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