Table Joins using 2 common fields

[sureshbaabu]

Hello,

I have a requirement to join 2 tables using 2 common fields.

My current script is as below: (Joined (left joined) two tables using column A)

Table1:

LOAD A,B  from Table1.xls;

Table2:

left join(Table1) LOAD A,C,D from Table2.xls;

***After joining my Table1 have A,B,C,D

Requirement: To join Table 1 and a new table 'Table 3' using A and C columns,

Table3:

LOAD A,C,X,Y  from Table3.xls;

When I load as above, it creates a Synthetic table using A and C - since it has more than 2 common fields.

So, I was trying to create a composite key using A and C (A&','&C as Key)

When I create the key on Table 1, Qlikview does not recognize column C, since C is coming from Table2.

I wanted create a new table (Say-Table 4) that holds all data after left joining Table2 with and Table1 as above and then create a composite key on Table3 and Table4

Could you please help me with the process to achieve the same?

Thank you

[#2]

The use of a synthetic key in this context is the correct one.

Have you read this discussion? http://community.qlik.com/thread/10279?start=30&tstart=0

[sureshbaabu]

Thank you for your response.

If Synthetic key is the only solution. I'm happy to go with the same.

If you could help me with the process to create a new table by merging 2 tables, that would be great.

(creating Table4 on my example above)

Thanks

[#4]

To confirm,

You want to create table 4 which is made of the data excluded by the joining of tables 1 and 2?

[sureshbaabu]

Yeah,,, I wanted to create Table4 which have all the values that is produced after joining Table1 and Table2

table4= table1+table2( Table2 left joined with table1)

[#6]

Think I know what you mean...

Table 4:

Load *

(composite key created here)

resident Table 1;

drop Table 1;

[sureshbaabu]

It doesnt seems to be working. I'm not sure if I'm wrong. When I drop Table1, Table 4  doesnt exist.

Note: the value gets doubled when i dont drop Table1.

I'm attaching my test file

Thanks

[danielact · Accepted solution]

If there are unique values of A and C in each table, try the following. If all values are in each table, you can leave out a few steps.

Table1a:

Load A,B from Table1.xls;

left join load A, C, D from Table2.xls;

Table1:

Load A&C as AC, B, D resident Table1a;

Table3a:

Load A,C, X, Y from Table3.xls;

Table3:

Load A&C as AC,X,Y resident Table3a;

LinkTable:

Load A,C,A&C as AC resident Table1a;

join Load A, C, A&C as AC resident Table3a;

drop table Table1a;

drop table Table3a;

[#9]

Danielact you're a star.

His is the correct answer to your original question

[sureshbaabu]

Thank you Both for helping me with the issue. Thanks for your time