Qlik Joins

[niranjana]

HI, I have to join 3 tables : 

AA:

Load

A,

B,

C,

D,

.

.,

E

Left join(AA)

BB:

Load

A,

I,

F

left join(AA)

load 

A,

B,

C,

J,

K.

The resultant table I get has fields:

A 

B

C

D

E

I

F

but i m not getting columns J and K. If i comment columns B and C in first table , then I get J and K columns in final table. 

The tables join on field A.

What is the reason for this.?

Thanks,

Niranjana

[jochem_zw]

Because B,C values are different from the values in table AA in field B,C…? Maybe try a outer join instead of the left join 

[niranjana]

No, most of the values of B and C columns present in Table AA are also present in last table. I cant do outer join as the requirement is I need the count (present in table 3 ) of values of A present in Table AA.

Column A has 10 rows in table AA and 50 rows in third table. I want the count of values of column A (that is 10 values). 

BUt commenting other common columns in first table works. 

Is there a way without commenting?

Thanks

[vinieme12 · Accepted solution]

Explanation below

The tables join on field A. << This is only true for the first left Join with  Table BB, during the second left Join the Tables will Join on Columns A,B & C

AA:

Load

A,

B,

C,

D,

.

.,

E

Left join(AA)

BB:

Load

A,

I,

F

After this join the result table has columns

A,B,C,D,E,I,F

left join(AA)

This Join will occur on all Common field that exist in AA and below table which are

A,B,C

load 

A,

B,

C,

J,

K.

If you want the Join key to always be the same field then create an alias field to join on

as below and don't load redundant fields

TableAA:

Load A,B,C,D,E, A as JoinKey

Left JOin(AA)

Load I,F,A as JoinKey

Left JOin(AA)

A as JoinKey ,J,K

[niranjana]

Hi,

Yes, If i dont include redundant fields in last table (that is B and C which are also present in first table) , I am getting the fields J and K. 

But why is it so?

Niranjana

[vinieme12]

unlike SQL db's where you explicitly specific the fields from LEft and Right table to join on

Qlik will automatically join on common fields from left and right table; this is by design

[niranjana]

Thanks for reply. But  I have another doubt: wont we  get wrong number of records if we exclude redundant fields i.e. B,C ? 

Also, left join gives duplicate records (As you said) but left keep doesn't. But in left keep, if there are two common fields they form synthetic keys.

Is there any workaround.? like using link table?...

but the requirement is I need to join tables in such a way that I get AGENT codes from first table to pass with second table.

Also I need to left join tables on 2 fields AgentID and QuoteDate. But it gives me too less records.

I have 

Table A:

AgentCode,

QuoteDate

Channel,

.......,

policyno;

left join

Table B:

AgentCode,

EntryDate as QuoteDate,

Status;

i out a table as 

AgentCode  Channel    Quotedate    count(Status)

in count i m getting duplciate rows if i use left join...

PLease guide

Niranjana