topic Re: How to show If condition when the variance ranges -5 to +5 in App Development

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

nevilledhamsiri — Thu, 19 Apr 2018 16:51:25 GMT

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

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

Gysbert_Wassenaar — Thu, 19 Apr 2018 16:57:59 GMT

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

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

felipedl — Thu, 19 Apr 2018 16:58:22 GMT

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

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

nevilledhamsiri — Thu, 19 Apr 2018 17:15:00 GMT

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