Optimized Join

[T_Qlik]

Hello Everyone,

Below code works for me on limited data set but on full load app throws Out of object Memory.

Is there better/optimize way to tackle below code?

Granularity of data is at month level and Join condition between two tables is below Composite Key.

without left join..numbers from Table 'ABC' are not giving the desired results..

ABC:

LOAD

AutoNumber(Month & '|' & Cust_ID & '|' & Brand) as Key,

Month,

    Status,

    Brand ,

    TOTAL_CUSTOMERS

;

select 

distinct Cust_ID,

Month,

    Status,

    Brand ,

    Count(distinct Cust_ID) TOTAL_CUSTOMERS 

from table1

Group by 1,2,3,4; 

left join (ABC)

XYZ:

LOAD 

    AutoNumber(Month & '|' & Cust_ID & '|' & Brand) as Key,

    Outlet_FLAG,

    Brand AS Brand_XYZ,

    Expected_Cust   //need to calculate count of customer where expected cust= 'Yes'..Count(Dist Cust_id)

FROM XYZ.qvd(qvd);

[Or]

Something that might be worth trying -

1) Store the Key field in the QVD directly rather than calculating it during the load

2) Use Where Exists Key in the QVD load

That would optimize the QVD load and reduce the rows immediately, which might help (and should be faster regardless).

[T_Qlik]

1. Possible for you to share an example.

2. Using Where Exists doesn't give the right count from table 'ABC' 

[marcus_sommer]

Please don't double-posting: optimize way to Join between two group by statemen... - Qlik Community - 2455080

[SunilChauhan]

ABC:

LOAD

AutoNumber(Month & '|' & Cust_ID & '|' & Brand) as Key,

Month,

    Status,

    Brand ,

    TOTAL_CUSTOMERS

;

select 

distinct Cust_ID,

Month,

    Status,

    Brand ,

    Count(distinct Cust_ID) TOTAL_CUSTOMERS 

from table1

Group by 1,2,3,4; 

Store ABC into  Path\ABC.QVD;  // type you location to store qvd

Drop table ABC;

XYZ:

LOAD 

    AutoNumber(Month & '|' & Cust_ID & '|' & Brand) as Key,

    Outlet_FLAG,

    Brand AS Brand_XYZ,

    Expected_Cust   //need to calculate count of customer where expected cust= 'Yes'..Count(Dist Cust_id)

FROM XYZ.qvd(qvd);

Store XYZ into  Path\XYZ .QVD;  // type you location to store qvd

Drop table ABC;

Test1:

LOAD

Key,

Month,

    Status,

    Brand ,

    TOTAL_CUSTOMERS  From PATh\ABC.QVD(QVD);

Left join(Test1)

LOAD 

 Key,

    Outlet_FLAG,

    Brand AS Brand_XYZ,

    Expected_Cust  

from Path/XYZ.QVD(QVD)

use expression
Count( Distinct {<Expected_Cust ={'Yes'}>} Cust_ID)

hope this works for you.