Joins on tables that have data from multiple QVD files

[#1]

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

[sridhar240784]

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