Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
I have 4 tables with the same columns that are updated every day.
Think of them as transaction data from 4 different bussinesses.
I used concatenate to load all of these tables into one General transactions table.
I now want to inner join this General transactions table with another table that contains customer data.
Unfortunately i want to keep only the COMMON customer data between these two tables.
Should i use inner join or something else?
Thanks in advance guys.
Hi,
try this:
Master_Tab:
load Key as Key1 from table5 ; //(say the key field is Key- which u're using to join)
Concatenated_Tables:
load * from table1 where exists(Key1,Key);
concatenate
load * from table2 where exists(Key1,Key);
concatenate
load * from table3 where exists(Key1,Key);
concatenate
load * from table4 where exists(Key1,Key);
drop table Master_tab;
Regards
First load your four transaction tables. Then do a
Left Keep Load ... From Customers;
or
Inner Keep Load ... From Customers;
if you really just want the intersection between the two.
HIC
The four concateneted tables create the General table with a total of 350.000 records.
The Second table has about 170.000 records.
I want to create a single table out of these two that will contain all of the 170.000 records of the second table and ONLY THE COMMON records from the General table.
My code looks something like this
[General Table]:
load * from table1;
concatenate (General Table)
load * from table2;
concatenate (General Table)
load * from table3;
concatenate (General Table)
load * from table4;
// total of 350.000 records.
[Second table] :
Load * from table 5;
// total of 170.000 records.
The final table should also have 170.000 records in total. All of the records from the [Second table] along with data from the common records between the two tables.
[General Table]:
load * from table1;
concatenate (General Table)
load * from table2;
concatenate (General Table)
load * from table3;
concatenate (General Table)
load * from table4;
// total of 350.000 records.
Right Join
[Second table] :
Load * from table 5;
// total of 170.000 records.
Hi Friend Use
[General Table]:
load * from table1;
concatenate (General Table)
load * from table2;
concatenate (General Table)
load * from table3;
concatenate (General Table)
load * from table4;
// total of 350.000 records.
right join
[Second table] :
Load * from table 5;
// total of 170.000 records.
regards
Kamal
I would strongly advice against joining the two tables. A "Right Keep" instead of a "Right Join" uses less memory and will most likely perform better.
See more on
http://community.qlik.com/blogs/qlikviewdesignblog/2012/09/12/to-join-or-not-to-join
http://community.qlik.com/docs/DOC-3412
HIC
Hi,
try this:
Master_Tab:
load Key as Key1 from table5 ; //(say the key field is Key- which u're using to join)
Concatenated_Tables:
load * from table1 where exists(Key1,Key);
concatenate
load * from table2 where exists(Key1,Key);
concatenate
load * from table3 where exists(Key1,Key);
concatenate
load * from table4 where exists(Key1,Key);
drop table Master_tab;
Regards