concatenating 4 tables into one and then inner join the resulted table with another one

[#1]

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.

[hic]

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

[#3]

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.

[rajni_batra]

[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.

[kamalqlik]

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

[hic]

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

[#7 · Accepted solution]

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