How to convert negative numbers to zero by borrowing from other positive numbers

[Bobjoy12]

How to convert negative numbers to zero by borrowing from the highest number in same category. Please find the example data below:-

Category	Dimension2	Value	Adjusted values
AB	ABC	-         50	0
AB	DEF	-         10	0
AB	GHI	-	0
AB	JKI	120	60
CD	ABC	-         100	0
CD	DEF	20	0
CD	GHI	80	0
CD	JKI	10	10

in the data above for Category 'AB'  the negative numbers -50 and -10 are converted to 0 by borrowing from 120.

For Category 'CD' the negative numbers -100 is converted to 0 by borrowing from 80 and 20.

Instead of using some long scripts is there anyway to achieve the required output.

[Arthur_Fong]

Try this in expression:

IF(
rank(Dimension2)=1,
RangeSum(above(Value,0,max(total aggr(rank(Dimension2),Category,Dimension2))))
,0)

Refer qvw attached as reference.

Thanks and regards,

Arthur Fong

[Bobjoy12]

Hi,

Thank you for your quick reply, but its not working as expected for the below sample, can you please help me figure out why.

EF	ABC	-	0
EF	DEF	10	10
EF	GHI	70	20
EF	JKI	-         50	0

[Arthur_Fong]

I don't see any problem at my side:

[Saravanan_Desingh]

One solution is..

tab1:
LOAD RowNo() As RowID,* INLINE [
    Category, Dimension2, Value
    AB, ABC, -50
    AB, DEF, -10
    AB, GHI, 
    AB, JKI, 120
    CD, ABC, -100
    CD, DEF, 20
    CD, GHI, 80
    CD, JKI, 10
    EF, ABC, 
    EF, DEF, 10
    EF, GHI, 70
    EF, JKI, -50
];

tab2:
LOAD *, If(Value>0,AutoNumber(RowNo(), Category)) As K1
Resident tab1
Order By Category, Value Desc
;

Left Join(tab2)
LOAD Category, Sum(If(Value<0,Value)) As K2
Resident tab2
Group By Category
;

tab3:
LOAD *, If(K1=1,Value+K2,Value+If(Peek(K3)>0,0,Peek(K3))) As K3
Resident tab2
Where Not IsNull(K1)
Order By Category, K1
;

Concatenate(tab3)
LOAD *
Resident tab2
Where IsNull(K1)
;

tab4:
LOAD *, Alt(If(K3>0,K3,0),0) As [Adjusted values]
Resident tab3
Order By RowID
;

Drop Table tab1, tab2, tab3;
Drop Field K1, K2, K3;

[Saravanan_Desingh]