3 Replies Latest reply: Jul 13, 2016 8:56 AM by Rudolf Linder RSS

    Scripting Issue

    Stephen Naude

      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;

        • Re: Scripting Issue
          Ruben Marin

          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
            Rudolf Linder

            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;