8 Replies Latest reply: Sep 26, 2014 3:02 AM by Geert Geladé RSS

    Scripting Problem

      Hi everyone,

       

             I have Name,Year, Commission and Cumulative sum fields.For each Commission End it should round of Cumulative sum Value.

       

      Forex:59897  to 6000

       

      Sample data

       

      PFA,

       

       

      Regards,

      Divya

        • Re: Scripting Problem
          Marco Wedel

          Hi Divya,

           

          is this the same question you asked in

          Re: Scripting Problem?

          or are these different requirements?

           

          regards

           

          Marco

          • Re: Scripting Problem
            Geert Geladé

            You get negative values due to the fact that  on change of "Name" or "Year" the cumulativesum starts again, so you have to take this into account when calculating CumulativeSum4.

             

            I changed your script (see below). Does this give the expected result in the field CumulativeSum5bis.

             

            a2:

            NoConcatenate

            load *,

            if(Commission<>peek(Commission) and len(peek(Commission))>0,1,0) as ChangeFlag2

            Resident Sample order by id desc;

             

            DROP Table Sample;

             

            a3:

            load *,

            if(ChangeFlag2=1,if(len(CumulativeSum)>5,left(CumulativeSum,2)+1,left(CumulativeSum,1)+1) & if(len(CumulativeSum)>5,repeat('0', len(CumulativeSum)-2),repeat('0', len(CumulativeSum)-1)), peek(CumulativeSum4)) as CumulativeSum4,

            if(Name<>peek(Name) or Year <> peek(Year),0,if(ChangeFlag2=1,if(len(CumulativeSum)>5,left(CumulativeSum,2)+1,left(CumulativeSum,1)+1) & if(len(CumulativeSum)>5,repeat('0', len(CumulativeSum)-2),repeat('0', len(CumulativeSum)-1)), peek(CumulativeSum4bis))) as CumulativeSum4bis,

            if(ChangeFlag2=1,left(CumulativeSum,1)+1 & repeat('0', len(CumulativeSum)-1), peek(CumulativeSum2)) as CumulativeSum2

            Resident a2

            order by id;

             

            DROP Table a2;

             

            a4:

            NoConcatenate

            load *,

            //if(ChangeFlag2=1,  CumulativeSum2, CumulativeSum - alt(CumulativeSum2,0)) as CumulativeSum3,

            if(ChangeFlag2=1,  CumulativeSum4, CumulativeSum - alt(CumulativeSum4,0)) as CumulativeSum5,

            if(Name<>peek(Name) or Year <> peek(Year),CumulativeSum,if(ChangeFlag2=1,  CumulativeSum4bis, CumulativeSum - alt(CumulativeSum4bis,0))) as CumulativeSum5bis

            Resident a3

            order by id;

             

            DROP Table a3;