topic Re: Scripting Problem in QlikView

Scripting Problem

Wed, 24 Sep 2014 18:47:29 GMT

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

MarcoWedel — Wed, 24 Sep 2014 22:20:45 GMT

Hi Divya,

is this the same question you asked in

Re: Scripting Problem?

or are these different requirements?

regards

Marco

Re: Scripting Problem

Thu, 25 Sep 2014 04:01:38 GMT

Hi Macro,

This is different requirement but related to that .

Pls Suggest me it is little urgent .

Regards,

Divya

Re: Scripting Problem

sujeetsingh — Thu, 25 Sep 2014 04:35:26 GMT

Come in mor words please.

Re: Scripting Problem

Thu, 25 Sep 2014 05:21:13 GMT

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

Re: Scripting Problem

MarcoWedel — Thu, 25 Sep 2014 07:24:05 GMT

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

Thu, 25 Sep 2014 07:28:37 GMT

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

Re: Scripting Problem

Fri, 26 Sep 2014 03:16:21 GMT

hi,

I am getting Negative values .pls suggest me. PFA

Re: Scripting Problem

geert_gelade — Fri, 26 Sep 2014 07:02:55 GMT

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;