Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Tommmy
Contributor III
Contributor III

conditional calculation

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?

Tommmy_0-1661952868187.png

 

Labels (4)
1 Solution

Accepted Solutions
BrunPierre
Partner - Master
Partner - Master

@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;

 

View solution in original post

3 Replies
Mark_Little
Luminary
Luminary

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
Contributor III
Contributor III
Author

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
Partner - Master
Partner - Master

@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;