Scripting Problem

[#1]

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

[MarcoWedel]

Hi Divya,

is this the same question you asked in

Re: Scripting Problem?

or are these different requirements?

regards

Marco

[#3]

Hi Macro,

This is different requirement but related to that .

Pls Suggest me it is little urgent .

Regards,

Divya

[sujeetsingh]

Come in mor words please.

[#5]

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.

Please suggest me

Regards,

Divya

[MarcoWedel]

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?

[#7]

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

[#8]

hi,

I am getting Negative values .pls suggest me. PFA

[geert_gelade]

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;