topic Re: Table Joins using 2 common fields in QlikView

Table Joins using 2 common fields

sureshbaabu — Mon, 24 Sep 2012 12:10:59 GMT

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

Re: Table Joins using 2 common fields

Mon, 24 Sep 2012 12:37:00 GMT

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

Re: Table Joins using 2 common fields

sureshbaabu — Mon, 24 Sep 2012 12:44:21 GMT

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

Re: Table Joins using 2 common fields

Mon, 24 Sep 2012 13:02:09 GMT

To confirm,

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

Re: Table Joins using 2 common fields

sureshbaabu — Mon, 24 Sep 2012 13:06:19 GMT

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)

Re: Table Joins using 2 common fields

Mon, 24 Sep 2012 13:30:58 GMT

Think I know what you mean...

Table 4:

Load *

(composite key created here)

resident Table 1;

drop Table 1;

Re: Table Joins using 2 common fields

sureshbaabu — Mon, 24 Sep 2012 14:01:38 GMT

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

Re: Table Joins using 2 common fields

danielact — Mon, 24 Sep 2012 14:45:59 GMT

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;

Re: Table Joins using 2 common fields

Fri, 28 Sep 2012 09:25:01 GMT

Danielact you're a star.

His is the correct answer to your original question

Re: Table Joins using 2 common fields

sureshbaabu — Fri, 28 Sep 2012 10:56:25 GMT

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

Re: Table Joins using 2 common fields

Fri, 28 Sep 2012 11:03:18 GMT

Glad to help, If you can mark Danielact's question as correct then other people with similar queries will be able to look at this discussion for the answer