Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
like
Load * from table1;
load * from table2;
Regards,
Kumar
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;
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
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.
I did concatenantion. But when pulling the column from second table in dimensions and measure column in expressions the report is keep on running.
Here one more thing sunny. I have multiple columns are common, So it is creating composite key and not giving any data.
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
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
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.
kumar avisakula wrote:
Can you answer me, is it correct or not.
What is correct or not?