Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi community,
I need to create a sum with a specific critéria.
Table example:
A | B | C | D
1 | 1 | 4 | 3
1 | 2 | 7 | 2
1 | 3 | 7 | 20
Result needed:
A | R
1 | 6
Result is calculated with the following condition: if (C-D > 0) then C-D else 0, so the "aux" table should be :
A | B | C - D with condition
1 | 1 | 1
1 | 2 | 5
1 | 3 | 0
And I need to sum.
HI @tnunes
does your test if (C-D > 0) then C-D else 0 have to be done on each row level separatelly?
If yes - your bet is to do it in load script during data load as that will yield much better performance:
If(C-D > 0, C-D, 0) as E
then in UI you just do Sum(E)
Hello @tnunes
Result needed:
Script:
Table:
LOAD * INLINE [
A, B, C, D
1, 1, 4, 3
1, 2, 7, 2
1, 3, 7, 20
];
// Create calculated column
TempTable:
LOAD *,
If(C-D > 0, C-D, 0) as [C-D_with_condition]
RESIDENT Table;
// Create new table with calculated column
ResultTable:
LOAD A,
Sum([C-D_with_condition]) as R
RESIDENT TempTable
GROUP BY A;
**If the Above solution is correct/appropriate replies as "solution". Please LIKE threads if the provided solution is helpful to.**
An alternative to the if() statement suggested by others is to use rangemax(), like this.
Table:
LOAD *, rangemax(C-D, 0) as R INLINE [
A, B, C, D
1, 1, 4, 3
1, 2, 7, 2
1, 3, 7, 20
];
Hi,
Thanks for all the replies.
I think using this in data load I will not have the result that I want since it depends on all filters.
I am trying to use something like "Sum(Aggr(Sum()))" , so Sum all the C-D, agreggate and Sum all to get A result.
Load * inline [
A | B | C | D
1 | 1 | 4 | 3
1 | 2 | 7 | 2
1 | 3 | 7 | 20] (delimiter is '|');
Measure would be:
Sum((C-D)*fabs(C>D))