topic Optimize Load script in QlikView

Optimize Load script

agni_gold — Sun, 11 Dec 2016 08:21:10 GMT

Hi Friends,

I have below code which is taking lots of time in loading because i have bulk data in fact ,

	Volume_Opp_Map_State:
	mapping load
		%State_Key,
		if(sum(ACTUALS_USD_BU)/1000<=0,1,0) as [Zero Volume]
	Resident Zonal_Fact
	where [fin_acct] = 'Total Volume'
	and Flag='Brand'
	group by %State_Key;

State:

Load distinct

	%State_Key,
		if(sum(ACTUALS_USD_BU)/1000<0 and applymap('Volume_Opp_Map_State',%State_Key,1)=0,1,0) as [Negative MACO State]
	Resident Zonal_Fact
	where [fin_acct] = 'MACO'
	and Flag='Brand'
	group by %State_Key,Flag;

can we write this in single load statement.

Re: Optimize Load script

MK_QSL — Sun, 11 Dec 2016 08:56:10 GMT

No, because fin_acct field having two different values.

Re: Optimize Load script

agni_gold — Sun, 11 Dec 2016 08:59:56 GMT

Thanks

Re: Optimize Load script

marcus_sommer — Sun, 11 Dec 2016 09:06:28 GMT

I would try to split your two loadings into four loadings by outsourcing the where-clause into a separate step. This meant as first the where-filter and then in following load the aggregation. Even if this are more load-steps it's often faster.

- Marcus

Re: Optimize Load script

agni_gold — Sun, 11 Dec 2016 09:08:14 GMT

Can you please explain more , i did not get you.

Re: Optimize Load script

vinieme12 — Sun, 11 Dec 2016 09:56:03 GMT

Try as below, broken in 3 steps:-

TEMP_State:

Load distinct

%State_Key,

sum(ACTUALS_USD_BU)/1000 as MACO_TOTAL

Resident Zonal_Fact
where [fin_acct] = 'MACO'
and Flag='Brand'

group by %State_Key,Flag;

LEFT JOIN (TEMP_State)

Volume_Opp_Map_State:
load

%State_Key,

sum(ACTUALS_USD_BU)/1000 as TOTAL_VOL

Resident Zonal_Fact
where [fin_acct] = 'Total Volume'
and Flag='Brand'
group by %State_Key;

NoConcatenate

State:

Load *,

if(MACO_TOTAL<0 and TOTAL_VOL>0,1,0) as [Negative MACO State]

Resident TEMP_State;