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

# 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

Hi Divya,

is this the same question you asked in

or are these different requirements?

regards

Marco

• ###### Re: Scripting Problem

Hi Macro,

This is different requirement but related to that .

Pls Suggest me it is little urgent .

Regards,

Divya

• ###### Re: Scripting Problem

Hi Sujeet,

The below screenshot shows my requirement.

After End of Each Commision Value it should round of respective cumulative sum value.

The above screen Shot conains Commision field ,At the end of that Commision value  ='0.01', it should round of thatCumulative sum to 60000.

I need New Cumulative Sum  Field that is requirement.

Regards,

Divya

• ###### Re: Scripting Problem

you mean you would like rounding 59897 up to 60000 not off to 6000 like you wrote in your initial post?

Is it always the next 10000 step you wish to round up to?

• ###### Re: Scripting Problem

Yes you are correct Macro rounding 59897 to60000(60k)

• ###### Re: Scripting Problem

hi,

I am getting Negative values .pls suggest me. PFA

• ###### Re: Scripting Problem

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

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

Resident Sample order by id desc;

DROP Table Sample;

a3:

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

//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;