Optimize Load script

[agni_gold]

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.

[MK_QSL]

No, because fin_acct field having two different values.

[agni_gold]

Thanks

[marcus_sommer]

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

[agni_gold]

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

[vinieme12 · Accepted solution]

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;