topic Re: How take change negative values as 0 in editor in QlikView

How take change negative values as 0 in editor

Anonymous — Wed, 30 May 2018 13:22:38 GMT

Hi,

I would like to change the negatives value of one dimension as 0 in the script.

Could any help me ?

Many thanks,

Lea

Re: How take change negative values as 0 in editor

sunny_talwar — Wed, 30 May 2018 14:34:12 GMT

May be this

RangeMax(FieldName, 0) as FieldName

Re: How take change negative values as 0 in editor

Anonymous — Wed, 30 May 2018 15:05:57 GMT

I would like to make all the negative value as 0.

eg.

Item Name, Sales

A, -3

B, 3

C, -1

D, 0

The data should be:

Item Name, Sales

A, 0

B, 3

C, 0

D, 0

Re: How take change negative values as 0 in editor

vishsaggi — Wed, 30 May 2018 15:13:16 GMT

May be try like

= IF(Sales < 0, 0, Sales)

Re: How take change negative values as 0 in editor

Anonymous — Wed, 30 May 2018 15:15:17 GMT

Could I use this directly in the scrip or should I add it to the calculated dimension:

=aggr(

if(Sum([Net Sales Amount]) <= fractile(TOTAL Aggr(Sum([Net Sales Amount]), [Item Description]), 0.1), 10,
if(Sum([Net Sales Amount]) <= fractile(TOTAL Aggr(Sum([Net Sales Amount]), [Item Description]), 0.2), 9,
if(Sum([Net Sales Amount]) <= fractile(TOTAL Aggr(Sum([Net Sales Amount]), [Item Description]), 0.3), 8,
if(Sum([Net Sales Amount]) <= fractile(TOTAL Aggr(Sum([Net Sales Amount]), [Item Description]), 0.4), 7,
if(Sum([Net Sales Amount]) <= fractile(TOTAL Aggr(Sum([Net Sales Amount]), [Item Description]), 0.5), 6,
if(Sum([Net Sales Amount]) <= fractile(TOTAL Aggr(Sum([Net Sales Amount]), [Item Description]), 0.6), 5,
if(Sum([Net Sales Amount]) <= fractile(TOTAL Aggr(Sum([Net Sales Amount]), [Item Description]), 0.7), 4,
if(Sum([Net Sales Amount]) <= fractile(TOTAL Aggr(Sum([Net Sales Amount]), [Item Description]), 0.8), 3,
if(Sum([Net Sales Amount]) <= fractile(TOTAL Aggr(Sum([Net Sales Amount]), [Item Description]), 0.9), 2,1))))))))),[Item Description])

Re: How take change negative values as 0 in editor

Anonymous — Wed, 30 May 2018 15:25:16 GMT

just use the function Sunny suggested.

Re: How take change negative values as 0 in editor

sunny_talwar — Wed, 30 May 2018 15:29:58 GMT

Where are you checking for negatives in this?

Re: How take change negative values as 0 in editor

Anonymous — Wed, 30 May 2018 15:31:19 GMT

I want to remove change negatives to 0 in Net Sale Amount

Re: How take change negative values as 0 in editor

sunny_talwar — Wed, 30 May 2018 15:33:30 GMT

May be this

=aggr(

if(Sum(RangeMax([Net Sales Amount], 0)) <= fractile(TOTAL Aggr(Sum(RangeMax([Net Sales Amount], 0)), [Item Description]), 0.1), 10,

if(Sum(RangeMax([Net Sales Amount], 0)) <= fractile(TOTAL Aggr(Sum(RangeMax([Net Sales Amount], 0)), [Item Description]), 0.2), 9,

if(Sum(RangeMax([Net Sales Amount], 0)) <= fractile(TOTAL Aggr(Sum(RangeMax([Net Sales Amount], 0)), [Item Description]), 0.3), 8,

if(Sum(RangeMax([Net Sales Amount], 0)) <= fractile(TOTAL Aggr(Sum(RangeMax([Net Sales Amount], 0)), [Item Description]), 0.4), 7,

if(Sum(RangeMax([Net Sales Amount], 0)) <= fractile(TOTAL Aggr(Sum(RangeMax([Net Sales Amount], 0)), [Item Description]), 0.5), 6,

if(Sum(RangeMax([Net Sales Amount], 0)) <= fractile(TOTAL Aggr(Sum(RangeMax([Net Sales Amount], 0)), [Item Description]), 0.6), 5,

if(Sum(RangeMax([Net Sales Amount], 0)) <= fractile(TOTAL Aggr(Sum(RangeMax([Net Sales Amount], 0)), [Item Description]), 0.7), 4,

if(Sum(RangeMax([Net Sales Amount], 0)) <= fractile(TOTAL Aggr(Sum(RangeMax([Net Sales Amount], 0)), [Item Description]), 0.8), 3,

if(Sum(RangeMax([Net Sales Amount], 0)) <= fractile(TOTAL Aggr(Sum(RangeMax([Net Sales Amount], 0)), [Item Description]), 0.9), 2,1))))))))),[Item Description])

Re: How take change negative values as 0 in editor

vishsaggi — Wed, 30 May 2018 15:38:22 GMT

The best preferred way is to use Sunny's RangeMax in your script. Like where ever you have your Net Sales Amount in your script. Like

LOAD Field1, Field2,....,

RangeMax([Net Sales Amount], 0) AS [Net Sales Amount]

FROM yoursourcetable;

Then you can use your aggr() calculated dim as is without any changes. If this is not what you looking for please elaborate.

Re: How take change negative values as 0 in editor

Anonymous — Wed, 30 May 2018 15:49:34 GMT

I worked, thanks !

Re: How take change negative values as 0 in editor

sunny_talwar — Wed, 30 May 2018 15:51:26 GMT

Funny, because I told you do the same thing above... but that did not work?

Re: How take change negative values as 0 in editor

vishsaggi — Wed, 30 May 2018 15:56:39 GMT

might have missed it.