Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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 |
I don't see any problem at my side:
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;