topic out of object memory on using join in App Development

out of object memory on using join

T_Qlik — Fri, 24 May 2024 14:17:18 GMT

Is there any way to get the fields 'Status, Brand, TOTAL_CUSTOMERS' from Table 'ABC' into table 'XYZ'

Distinct combination of Month & '|' & Cust_ID & '|' & Brand from table 'ABC' is 140 Million

and in table 'XYZ' is 60 Million.

left join works on small data set. I have also tried using Applymap() but in both cases ran into

out of object memory error

ABC:

LOAD

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

AutoNumberHash128(Month, Cust_ID, Brand)as Key,

Brand as Brand_XYZ,

Expected_Cust

FROM XYZ.qvd(qvd);

Re: out of object memory on using join

Sayed_Mannan — Fri, 24 May 2024 18:28:48 GMT

Given the large size of your data, using JOIN or ApplyMap() might lead to an 'Out of Object Memory' error in Qlik Sense. Instead, you can use INTERVALMATCH which is more memory-efficient for large datasets. Here's how you can modify your script:

ABC:
LOAD
AutoNumberHash128(Month, Cust_ID, Brand) as Key,
Month,
Status,
Brand,
TOTAL_CUSTOMERS;

SQL select
distinct Cust_ID,
Month,
Status,
Brand,
Count(distinct Cust_ID) TOTAL_CUSTOMERS
from table1
Group by 1,2,3,4;

XYZ:
LOAD
AutoNumberHash128(Month, Cust_ID, Brand)as Key,
Brand as Brand_XYZ,
Expected_Cust
FROM XYZ.qvd(qvd);

// Use IntervalMatch to link the tables
LinkTable:
IntervalMatch(Key)
LOAD Key
RESIDENT XYZ;

JOIN(LinkTable)
LOAD Key
RESIDENT ABC;

In this script, IntervalMatch creates a link table that connects the Key fields from both ABC and XYZ. This way, you can get the fields Status, Brand, TOTAL_CUSTOMERS from table 'ABC' into table 'XYZ' without running into memory issues.

Please replace the SQL statement with the appropriate data loading statement for your environment. Also, remember to replace `table1` and `XYZ.qvd` with your actual table name and QVD file path, respectively.

Remember to drop the `LinkTable` after you are done with it to free up memory. You can do this by adding `DROP Table LinkTable;` at the end of your script.

Please note that this solution assumes that the `Key` field uniquely identifies each record in both tables. If that's not the case, you might need to adjust the script accordingly.

If you're still having trouble, I recommend reaching out to Qlik Support or the Qlik Community for further assistance.

Re: out of object memory on using join

T_Qlik — Thu, 30 May 2024 11:51:10 GMT

Interval Fields List not matching number of fields.

Re: out of object memory on using join

Rohan — Fri, 31 May 2024 09:18:51 GMT

Hi @T_Qlik ,

It seems the join operation is completely utilizing your memory & resources. Hence, the script is failing. Trying increasing your resources of the server. Also, we have an amazing product which can help you identify such issues : "Wowizer". You can reach out to @nirav_bhimani if interested further.

Regards,

Rohan.

Re: out of object memory on using join

WillBit — Fri, 31 May 2024 09:51:03 GMT

These tasks should be done by ETL on databases. In my experience, database procedures are really faster compared to Qlik embedded ETL functionalities.

Re: out of object memory on using join

marcus_sommer — Fri, 31 May 2024 10:13:42 GMT

Did you consider my suggestions from here: Re: optimize way to Join between two group by stat... - Qlik Community - 2455080 by checking the size of the data-sets without this transformation because your available resources might be already entirely consumed. If so an appropriate preparing of the script (only this task) and/or of the environment (no other parallel consuming tools) would be useful as well as considerations to increase the RAM.

Beside this you could avoid all this trouble by using an incremental approach and slicing the data on a month-level. This wouldn't only reduce the number of records within the n iterations else also simplifying the key because only Cust_ID and Brand would be remain for it.