topic Re: Scripting Issue in QlikView

Scripting Issue

Wed, 13 Jul 2016 12:39:34 GMT

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;

Re: Scripting Issue

rubenmarin — Wed, 13 Jul 2016 12:53:06 GMT

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.

Re: Scripting Issue

Wed, 13 Jul 2016 12:55:18 GMT

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

Re: Scripting Issue

Anonymous — Wed, 13 Jul 2016 12:56:26 GMT

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;