How to show If condition when the variance ranges -5 to +5

[nevilledhamsiri]

Hi friends,

If I am to say when BALAMT-Balance ranges -5 to +5, ok  otherwise not ok.

LOAD

*,IF((BALAMT-Balance) <>=0,'NO','OK')AS OK_NOT

Please correct the above condition in order to satisfy requested condition

Thanks

Neville

[Gysbert_Wassenaar · Accepted solution]

IF(BALAMT-Balance >= -5 and BALAMT-Balance <=5 ,'OK','NO')AS OK_NOT

[felipedl]

Use set analysis for it.

Example:

Table:

load * Inline

[

Material,Group

1A,1

2A,1

3B,2

4B,3

5B,1

];

data:

Load * Inline

[

Material,Customer,Organization,Seller, Order Value

1A,Yellow,   Steel,   Mary,  5   

2A,Blue,Wood, Joe,     10 

5X ,Black,    Liquid , Victor,  0   

7Q, Purple,   Liquid , Mark,   0  

3B, Yellow,   Steel,   Mary ,  7  

4B, Red,Gold,    Sue,    -25

5B,Red,Gold,    Sue ,   -5

];

Gives the full table bellow:

Material	Group	Customer	Organization	Seller	Order Value
5X		Black	Liquid	Victor	0
2A	1	Blue	Wood	Joe	10
7Q		Purple	Liquid	Mark	0
5B	1	Red	Gold	Sue	-5
4B	3	Red	Gold	Sue	-25
1A	1	Yellow	Steel	Mary	5
3B	2	Yellow	Steel	Mary	7

This code with generated data, and the expression: sum({<[Order Value]={">=-5<=5"}>}[Order Value])

gives the following table:

Customer	sum({<[Order Value]={">=-5<=5"}>}[Order Value])
	0
Red	-5
Yellow	5

IF you create a flag in load statement you can do

data:

Load

*,

if ([Order Value]>=-5 and [Order Value]<=5,1,0) as [Flag];

Load * Inline

[

Material,Customer,Organization,Seller, Order Value 

1A,Yellow,   Steel,   Mary,  5    

2A,Blue,Wood, Joe,     10  

5X ,Black,    Liquid , Victor,  0    

7Q, Purple,   Liquid , Mark,   0   

3B, Yellow,   Steel,   Mary ,  7   

4B, Red,Gold,    Sue,    -25 

5B,Red,Gold,    Sue ,   -5 

];

and use the expression: sum({<Flag={1}>}[Order Value])

to get the same result:

Customer	sum({<Flag={1}>}[Order Value])
	0
Red	-5
Yellow	5

[nevilledhamsiri]

Hi Felip

Thanks a lot for your lengthy explanation where this could be resolved through set expression. This will obviously help me in the course of future analysis.

Thanks to Gysbert too for your explanation.

All your solutions are warmly welcomed!

Many Thanks

Neville