conditional calculation

[Tommmy]

Hi experts

As dimension there is 'Material'.  In our case, material is '900'.

The formula should calculate if 'Quantity' is 0.00 the stock value should be zero too.

Any ideas?

[Mark_Little]

HI @Tommmy 

Might not be understanding fully, but i would think you should just need to take you existing set analysis from the stock value field and wrap it in an if statement 

IF( Quantity = 0, 0, 'your expression')

[Tommmy]

this won't work. Only if the total of quantity of a material ID is zero then the result for value should be zero too.

[BrunPierre · Accepted solution]

@Tommmy I'd do something like this

Data:
load * Inline[
MaterialID, Quantity,Value
A1,100,200
B1,200,400
C3,50,70,
D1,70,114
D1,-70,56
];

LOAD *,
IF(Quantity=0,0,Value) as tempValue;

NoConcatenate
load MaterialID,
Sum(Quantity) as Quantity,
Sum(Value) as Value

Resident Data
Group by MaterialID;
Drop table Data;

DROP Field Value;

RENAME Field tempValue to Value;

EXIT SCRIPT;