Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Divya,
is this the same question you asked in
or are these different requirements?
regards
Marco
Hi Macro,
This is different requirement but related to that .
Pls Suggest me it is little urgent .
Regards,
Divya
Come in mor words please.
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
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?
Yes you are correct Macro rounding 59897 to60000(60k)
hi,
I am getting Negative values .pls suggest me. PFA
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;