topic Re: Join in QlikView

Join

Anonymous — Thu, 21 Jan 2016 13:06:26 GMT

Hi,

I am learning Qlikview and I have a basic doubt abot Join.

I have two tables which are 20 columns in common. I want to make join for this two tables.

In database it is inner join between two tables. If I want to achieve the same should I use Inner Join between two tables. Or simply

Load * from table1;

load * from table2;

Regards,

Kumar

Re: Join

sunny_talwar — Thu, 21 Jan 2016 13:10:14 GMT

For Inner Join, try this:

Table:

LOAD *

From table1;

Inner Join (Table)

LOAD *

From table2;

Leaving them unjoined will be equivalent of full outer join.

But if you want to leave them as two separate tables and still keep only those observations that are available in both the tables you can try inner keep:

Table1:

LOAD *

From table1;

Table2:

Inner Keep (Table)

LOAD *

From table2;

Re: Join

marcus_sommer — Thu, 21 Jan 2016 13:16:33 GMT

If many columns are in common then is a concatenate (similar to union in SQL) from both tables most often a better way then a join.

- Marcus

Re: Join

Anonymous — Thu, 21 Jan 2016 13:20:05 GMT

Thanks Sunny for ur quick reply.

I want to make two tables as single table. But when do

table1:

load * from

table1;

inner join(table1)

load * from table2;

It is appearing me as single table. But when I pull a column from second table it is not giving any data corresponding to that fields.

Re: Join

Anonymous — Thu, 21 Jan 2016 13:21:16 GMT

I did concatenantion. But when pulling the column from second table in dimensions and measure column in expressions the report is keep on running.

Re: Join

Anonymous — Thu, 21 Jan 2016 13:22:12 GMT

Here one more thing sunny. I have multiple columns are common, So it is creating composite key and not giving any data.

Re: Join

marcus_sommer — Thu, 21 Jan 2016 13:24:11 GMT

Maybe there is something other wrong and you have create synthetic keys and/or circular references - have a look here: Get started with developing qlik datamodels

- Marcus

Re: Join

sunny_talwar — Thu, 21 Jan 2016 13:24:20 GMT

That could be because one or more of the 20 field names it must be joining on doesn't have matching information. The join will only work if those 20 column matches in value (even the case needs to be the same) for them to join correctly.

Another problem could be related to Null Values. Null's don't join the way you would expect. The work around that could be to create a Key to join

Table1:

LOAD AutoNumber(Field1&'|'&Field2&'|'&Field3.....) as Key

Field1 as Table1.Field1

Field2 as Table1.Field2

Inner Join (Table1)

LOAD AutoNumber(Field1&'|'&Field2&'|'&Field3.....) as Key

Field1 as Table2.Field1

Field2 as Table2.Field2

Re: Join

Anonymous — Thu, 21 Jan 2016 13:29:24 GMT

commented some of the columns from one table which are common that is also not working. Can you answer me, is it correct or not.

Re: Join

sunny_talwar — Thu, 21 Jan 2016 13:31:31 GMT

kumar avisakula wrote:

Can you answer me, is it correct or not.

What is correct or not?

Re: Join

Anonymous — Thu, 21 Jan 2016 13:48:48 GMT

If I comment same columns in one of the table is also fine.

Re: Join

sunny_talwar — Thu, 21 Jan 2016 13:51:04 GMT

Yes, it should be.

Table1:

LOAD AutoNumber(Field1&'|'&Field2&'|'&Field3.....) as Key

Field1

Field2

Inner Join (Table1)

LOAD AutoNumber(Field1&'|'&Field2&'|'&Field3.....) as Key

Re: Join

Anonymous — Thu, 21 Jan 2016 13:59:57 GMT

After commenting common columns fields except one key field, is it required to concatenate common columns Sunny?

Re: Join

Thu, 21 Jan 2016 14:25:43 GMT

Qlikview will perform Join like in database sql queries. The other option in Qlikview is Concatenate and it works like UNION in SQL.

Based on your requirement you can use Join or concatenate. Before take which operation need to perform, you need to understand the tables relation with respect to your business. If two tables have fact then you can simply concatenate. One table is Fact & other table is Dimension table I would recommend Join.