topic Re: optimize way to Join between two group by statements in App Development

optimize way to Join between two group by statements

T_Qlik — Wed, 22 May 2024 09:17:11 GMT

Hello Everyone,

Below code works for me on limited data set but on full load app throws Object out of 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);

Re: optimize way to Join between two group by statements

ali_hijazi — Wed, 22 May 2024 09:45:17 GMT

you can simply keep the tables as are
qlik will link them via Key
and you calculate the count on the UI

Count(Distict {<Expected_Cust={"yes"}>} Cust_id)

Re: optimize way to Join between two group by statements

T_Qlik — Wed, 22 May 2024 09:47:17 GMT

I know but in my case data volume is on higher side and app throws out of object memory error

Re: optimize way to Join between two group by statements

T_Qlik — Wed, 22 May 2024 09:48:43 GMT

Plus without left join count from table 'ABC' is not giving the desired O/P.

Re: optimize way to Join between two group by statements

marcus_sommer — Wed, 22 May 2024 10:33:23 GMT

It's not clear for me - the load runs into a memory error or afterwards any UI object?

Re: optimize way to Join between two group by statements

T_Qlik — Wed, 22 May 2024 10:41:13 GMT

Load runs into memory error

Re: optimize way to Join between two group by statements

marcus_sommer — Wed, 22 May 2024 11:20:27 GMT

The load with just a few fields and the group by inside of the sql looked rather small and I wouldn't expect memory issues unless the datasets are really huge and going into billions of records. Therefore how many records are there and how many free RAM is available? Further make sure that there are no further data within the data-model before you perform this joining.

Re: optimize way to Join between two group by statements

T_Qlik — Wed, 22 May 2024 12:21:54 GMT

Close to 1.3 billion records from table 'ABC'.

Re: optimize way to Join between two group by statements

marcus_sommer — Wed, 22 May 2024 13:07:36 GMT

There are several approaches thinkable which may reduce the RAM consumption but the most obvious one would be to implement an incremental logic.

Personally I would tend to distribute such task to at least two layers and the first one would be the slicing of ABC in YYYYMM periods and storing them into qvd's. Similar would it be with the XYZ join-data and the join would then be applied within the next layer.

Beside this you may try to remove the autonumber() from the joins because I'm not sure at which work-step the autonumber() released the RAM. Also a storing of the entire ABC as qvd is an option and would mean that the autonumber() couldn't remain. Depending on the Cust_ID and Brand you might be able to create a numeric concatenation instead of a string one.

A further reducing might be the filtering of the join-table against the existing ABC key-values - maybe also in a workstep in beforehand and then loading it as mapping table and merging then the data per:

subfield(applymap())

instead of the join.

Before you goes in any direction it would be helpful just to load ABC and the looking how many RAM is consumed and of course further available to get a feeling if your environment is general suitable sized for your wanted data-set.

Re: optimize way to Join between two group by statements

rwunderlich — Wed, 22 May 2024 14:08:11 GMT

A couple of thoughts.

AutoNumberHash128(Month, Cust_ID, Brand) as Key

may use less memory than AutoNumber.

You mentioned you were not getting correct results when kept as separate tables. That may be because you have extra rows that are remove by the Left Join. Try keeping as separate tables but with a where exists() to filter.

XYZ:

LOAD

AutoNumberHash128(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)

Where Exists(Key, AutoNumberHash128(Month, Cust_ID, Brand) )
;

1.3 Billion rows can be challenging 🙂

-Rob