Scripting Issue

[#1]

Hi

Can someone please explain to me why this is creating an error

left join (temp1)
LOAD  HIN,
Channel,
left([Trans_Date],6) as Period,
count([Trans_Date]) as Transactions,
sum(if([Transaction_type]='Withdrawal' and [ResultCode]=0,1,0)) as Withdrawels,
sum(if([Transaction_type]<>'Withdrawal',1,0)) as Balance_Enquiries,
sum(if([Transaction_type]='Withdrawal' and [ResultCode]=0,[CompletionAmount],0)) as Withdrawel_Amount
resident NPS_ATM;

[rubenmarin · Accepted solution]

Hi, you have Sum's and Count's, those funtions needs a group by clause by any other field not included in these functions, this is:

...

resident NPS_ATM

Group by HIN, Channel, Period;

To avoid use of Trans_Date in the group by you can create the 'Period' field in the NPS_ATM table. You can create the field with another name, so you can easily drop it later.

[#3]

Thanks...I just earned the blonde award of the day

[Anonymous]

you use a grouping function (sum) without group

please check if the sum is necessary

Maybe it is easier to define a temporary table using Group by

and then join the table

TEMP:

LOAD  HIN,
Channel,
left([Trans_Date],6) as Period,
count([Trans_Date]) as Transactions,
sum(if([Transaction_type]='Withdrawal' and [ResultCode]=0,1,0)) as Withdrawels,
sum(if([Transaction_type]<>'Withdrawal',1,0)) as Balance_Enquiries,
sum(if([Transaction_type]='Withdrawal' and [ResultCode]=0,[CompletionAmount],0)) as Withdrawel_Amount
resident NPS_ATM

Group by HIN, Channel

left join(temp1)

load *

resident TEMP;

drop table TEMP;