Loading from resident table problem - Qlik Community

stevietm · ‎2017-09-08

Hi all,

Basically what i am doing is concatenating 2 table then joining a 3rd table, then i try to load the final table (combination of the 3) but it keeps running forever and the memory just keeps climbing (i am working on a server with 64gig ram). So the first and second table is my transaction tables which i concatenate, then the 3rd table is a customer grouping table which i left join to the transactions. I do this so i can create a key on the transaction month and customer group in order to link budgets to the transactions. Hope this make sense.

Total lines for the concatenation - 42 000

Left join table contains 33mil loaded from a QVD

Currently memory usage is sitting on 14gig and climbing

See below my script. Not sure if i am doing anything wrong as when i debug the script it runs fine.

//------------------------------------------------------------------------------LOAD_VIRTUAL_TABLES---------------------------------------------------------------------------------

//------------------------------------------------------------------------------IMPORT_CIB--------------------------------------------------------------------------------------------

TRANSACTIONS:

//CIB:

LOAD YEAR_MONTH as CIB_YEAR_MONTH,

// Date#(YEAR_MONTH)&'-'&'01' as DATE_KEY,

YEAR_MONTH,

CUST_ID,

CUST_CODE,

Upper(CUST_NAME) as CUST_NAME,

REBATE,

QTY_ACT,

CUST_COMM,

VAT_REG,

SUPP_COMM,

If(CUST_CODE='TYR002',SUPP_COMM = '0',

If(CUST_CODE='TYR003',SUPP_COMM = '0',

if(CUST_CODE='BAS001',SUPP_COMM*0.249373434,

if(CUST_CODE='BAS002',SUPP_COMM*0.214285714,

if(CUST_CODE='BAS003',SUPP_COMM*0.249373434,

if(CUST_CODE='BAS004',SUPP_COMM*0.186716791,

QTY_ACT* REBATE/1.14)))))) as DEALER_PAYABLE,

If(CUST_CODE='TYR002',SUPP_COMM = '0',

If(CUST_CODE='TYR003',SUPP_COMM = '0',

if(CUST_CODE='BAS001',SUPP_COMM = '0',

if(CUST_CODE='BAS002',SUPP_COMM = '0',

if(CUST_CODE='BAS003',SUPP_COMM = '0',

if(CUST_CODE='BAS004',SUPP_COMM = '0',

SUPP_COMM)))))) as TOTAL_COMM,

If(CUST_CODE='TYR002',SUPP_COMM = '0',

If(CUST_CODE='TYR003',SUPP_COMM = '0',

if(CUST_CODE='TROP02',SUPP_COMM*0.249373434,

if(CUST_CODE='ERZ002',SUPP_COMM*0.214285714,

if( wildmatch(CUST_CODE,'IDL001-*'), SUPP_COMM*0.374686716,

if( wildmatch(CUST_CODE,'ONT007-*'), SUPP_COMM*0.186716791,

if(CUST_CODE='ONT008',SUPP_COMM*0.186716791,

if(CUST_CODE='BAS001',SUPP_COMM*0.000000001,

if(CUST_CODE='BAS002',SUPP_COMM*0.000000001,

if(CUST_CODE='BAS003',SUPP_COMM*0.000000001,

if(CUST_CODE='BAS004',SUPP_COMM*0.000000001,

SUPP_COMM- QTY_ACT* REBATE/1.14))))))))))) as CIB_RETAINED

FROM

(qvd);

Concatenate

//------------------------------------------------------------------------------IMPORT_OGR--------------------------------------------------------------------------------------------

//OGR:

LOAD YEAR_MONTH as OGR_YEAR_MONTH,

//Date#(YEAR_MONTH)&'-'&'01' as DATE_KEY,

YEAR_MONTH,

CUST_ID,

CUST_CODE,

Upper(CUST_NAME) as CUST_NAME,

CUST_OGR,

if(CUST_CODE='TYR002',CUST_OGR = '0', if(CUST_CODE='TYR003',CUST_OGR = '0',CUST_OGR/100)) as REVENUE_%,

RECHARGE,

if(CUST_CODE='TYR002',RECHARGE = '0',

if(CUST_CODE='TYR003',RECHARGE = '0',

if(CUST_CODE='BAS001',RECHARGE = '0',

if(CUST_CODE='BAS002',RECHARGE = '0',

if(CUST_CODE='BAS003',RECHARGE = '0',

if(CUST_CODE='BAS004',RECHARGE = '0',

(((RECHARGE))))/1.14))))) as TOTAL_RECHARGE,

if(CUST_CODE='TYR002',RECHARGE*0.0000000000001,

if(CUST_CODE='TYR003',RECHARGE*0.0000000000001,

if(CUST_CODE='BAS001',RECHARGE*0.0000000000001,

if(CUST_CODE='BAS002',RECHARGE*0.0000000000001,

if(CUST_CODE='BAS003',RECHARGE*0.0000000000001,

if(CUST_CODE='BAS004',RECHARGE*0.0000000000001,

Round(RECHARGE))*0.13/1.14))))) as COMM,

if(CUST_CODE='TYR002',RECHARGE = '0',

if(CUST_CODE='TYR003',RECHARGE = '0',

(CUST_REV)))/1.14 as TOTAL_DEALER,

if(CUST_CODE='TYR002',RECHARGE = '0',

if(CUST_CODE='TYR003',RECHARGE = '0',

if(CUST_CODE='BAS001',RECHARGE = '0',

if(CUST_CODE='BAS002',RECHARGE = '0',

if(CUST_CODE='BAS003',RECHARGE = '0',

if(CUST_CODE='BAS004',RECHARGE = '0',

if(CUST_CODE='ERZ002',(RECHARGE * 0.015)/1.14,

if(CUST_CODE='TROP02',(RECHARGE * 0.015)/1.14,

if( wildmatch(CUST_CODE,'IDL001-*') ,(RECHARGE * 0.035) / 1.14,

if( wildmatch(CUST_CODE,'ONT007-*') ,(RECHARGE * 0.025) / 1.14,

if(CUST_CODE='ONT008',(RECHARGE * 0.025)/1.14,

(((RECHARGE * 0.13) / 1.14) - CUST_REV / 1.14)))))))))))) as OGR_RETAINED,

INCOME,

CUST_REV,

VAT_REG

FROM

(qvd);

//------------------------------------------------------------------------------IMPORT_SCANNER--------------------------------------------------------------------------------------------

Left Join

//SCANER:

LOAD Upper(SALES_PERSON_NAME) as SALES_PERSON_NAME,

[CUST_CODE],

Upper([CUST_NAME]) as S_CUST_NAME,

Upper([REPORT_GROUP_NAME]) as REPORT_GROUP_NAME,

[PRELOADED_AMOUNT] as S_PRELOADED_AMOUNT,

Upper([SILO]) as SILO,

Upper([SUB_GROUP]) as SUB_GROUP,

Upper([GROUP]) as GROUP,

[BUDGET_GROUP] as OC_BUDGET_GROUP_ID,

Upper([DEALER]) as DEALER

FROM

(qvd);

//------------------------------------------------------------------------------LOAD_FACT_TABLE------------------------------------------------------------------------

LOAD CIB_YEAR_MONTH,

YEAR_MONTH,

YEAR_MONTH&OC_BUDGET_GROUP_ID as KEY,

CUST_ID,

CUST_CODE,

CUST_NAME,

REBATE,

QTY_ACT,

CUST_COMM,

SUPP_COMM,

DEALER_PAYABLE,

TOTAL_COMM,

CIB_RETAINED,

OGR_YEAR_MONTH,

CUST_OGR,

REVENUE_%,

RECHARGE,

TOTAL_RECHARGE,

COMM,

TOTAL_DEALER,

OGR_RETAINED,

INCOME,

CUST_REV,

VAT_REG,

SALES_PERSON_NAME,

S_CUST_NAME,

REPORT_GROUP_NAME,

S_PRELOADED_AMOUNT,

SILO,

SUB_GROUP,

GROUP,

DEALER

Resident TRANSACTIONS;

//-------------------------------------------------------------------------------DROP_VIRTUAL_TABLE------------------------------------------------------------------------------------------

DROP Table TRANSACTIONS;

//--------------------------------------------------------------------------------BUDGETS----------------------------------------------------------------------------------------------------

BUDGETS:

LOAD YEAR_MONTH&BUDGET_GROUP_ID as KEY,

BUDGET_SEGMENT,

BUDGET_GROUP_ID as OC_BUDGET_GROUP_ID,

BUDGET_VALUE

FROM

(ooxml, embedded labels, table is Sheet1);

sonkumamon · ‎2017-09-08

Try adding NoConcatenate function before the Resident Load,

maybe you have the same number of fields in the 2 tables and Qlikview automatically concatenate them.

prieper · ‎2017-09-08

You may also try to load with just 10 or 100 records and have a look at the datamodels and check, what is not correct.

In scripting it make always sense to be precise and use CONCATENATE resp NOCONCATENATE expressively.

Looks as if you are trying to JOIN over multiple fields, thus creating huge cartesian products.

stevietm · ‎2017-09-08

Hi Both

I have tried the noconcatenate but still the same problem, even when i load on 100 or 1000 up to a mill the script finishes and all looks fine after the load. But not on total load.

Maybe i should just run the concatenate and left join in a qvd and load from there instead of doing a resident load?

Peter_Cammaert · ‎2017-09-08

With a reduced load (100 records from the initial two tables, but 33mil from the 3rd one), try to get an idea of what is happening in the LEFT JOIN. Do you get a correct number of resultant rows, or does the initial table explode. Then try to predict the number of rows you would get if you combined 42000 rows with 33mil rows. Does it still match the expected outcome?

You may want to review the logic behind the LEFT JOIN, as it may attempt to perform a hugely expensive operation.