Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Scripting Issue

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;

1 Solution

Accepted Solutions

Re: Scripting Issue

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 Replies

Re: Scripting Issue

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.

Not applicable

Re: Scripting Issue

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

hrlinder
Honored Contributor

Re: Scripting Issue

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;

Community Browser