Joins on tables that have data from multiple QVD files

Thu, 29 Dec 2011 07:54:00 GMT

Hi,

I have monthly QVD files for Sales and Region table. I want to load all the QVDs for Sales and Region and then perform a INNER JOIN between them. I am doing following things -

SALES:

LOAD

REGION_ID,

SALES_AMT

FROM [../FolderName/SALES_*.QVD] (QVD);

INNER JOIN (SALES)

LOAD

REGION_ID,

REGION

FROM [../FolderName/REGION_*.QVD] (QVD);

However this join is not giving the correct data. In order to work this I have to create resident tables and then perform join on resident tables which is shown below -

SALES:

LOAD

REGION_ID,

SALES_AMT

FROM [../FolderName/SALES_*.QVD] (QVD);

REGION:

LOAD

REGION_ID,

REGION

FROM [../FolderName/REGION_*.QVD] (QVD);

RESIDENT_SALES:

LOAD

REGION_ID AS RID,

SALES_AMT

RESIDENT SALES;

DROP TABLE SALES;

INNER JOIN (RESIDENT_SALES)

LOAD

REGION_ID AS RID,

REGION

RESIDENT REGION;

DROP TABLE REGION;

Is there a better way to achieve this without resident tables ? This approach takes lot of time when i have high volumes of data in Region and Sales table.

Thanks,

Samir

Re: Joins on tables that have data from multiple QVD files

sridhar240784 — Thu, 29 Dec 2011 09:29:06 GMT

Hi,

When you use * (WildChar) to load multiple files in one table, QlikView internally load all the tables separately and then concatenates tables to root table, Since the field names are same.

Additional note – QlikView loads the entire table in your application 1^stand perform all Joins/Concatenation at last when script execution finishes.

Meaning to say, for example you have 2 table for sales (Sales_1&Sales_2) and 2tables for Region (Region_1 & Region_2).

1) QlikView LoadsAll tables 1^st in order i.e. Sales_1 , Sales_2 , Region_1 &Region_2

2) Now QlikView performsJoin/Concatenate. i.e. Load Sales_1 and Concatenate the Sales_2 table to Sales_1 table since the field namesare same.

3) Next step in ourcode has ‘Join’, so QlikView loads the Region_1 and joins the same to the resultant Sales table.

4) Next step QlikView Will load the Region_2 and will try to concatenate the Region_2 table with the resultant Sales table but field name are not same, it links the Region_2 table with sales table with composite key.Here Joining between the tables are happening before the Concatenation. If you see in your data model, you may have synthetic key because the same field name.

5) To avoid this,you may load the entire table with * (WildChar) 1^st and can do the joining.

You may also try something like below one.

SALES:

LOAD

REGION_ID,

SALES_AMT

FROM [../FolderName/SALES_*.QVD] (QVD);

Region:

LOAD

REGION_ID,

REGION

FROM [../FolderName/REGION_*.QVD] (QVD);

INNER JOIN(SALES)

LOAD * RESIDENT Region;

DROP TABLE Region;

Hope i made it Clear

-Sridhar

topic Re: Joins on tables that have data from multiple QVD files in QlikView

Joins on tables that have data from multiple QVD files

Re: Joins on tables that have data from multiple QVD files