Null Value recognition in syntax Expression

[#1]

I’ve got two products that are sold across many retailers. Each retailer assigns product preference code.

I’m comparing preference code for each product by retailer, by creating FLAG column using Expression with a simple P, N, E  (P = Preferred, N = Not Preferred,

E = Equal status). I’m using IF statement similar to this:

= IF ( PROD_A  = ‘P’ AND PROD_B = ‘P’, ‘ E’,         /* if PROD_A & PROD_B = ‘P’ status = ‘E’*/

   IF  (PROD_A = ‘N’ AND PROD_B = ‘P’, ‘N’,

   IF (PROD_A  = NULL() AND PROD_B = ‘P’, ‘N’,’P’)))

My problem is with a null value recognition when there is no P,N,E assigned to PROD_A or PROD_B.

I tried using null expression for PROD_A, however based on the above syntax I get P for PROD_A, while it should be flagged as N.

[MayilVahanan · Accepted solution]

HI

Try like this

isNull(FieldName) or

len(Trim(FieldName))=0

or

FieldName =''

By using any of the above, you can achieve your requirement

Hope it helps

[CELAMBARASAN]

Hi,

Check with this

=IF(PROD_A  = ‘P’ AND PROD_B = ‘P’, ‘ E’,

IF((PROD_A = ‘N’ OR IsNull(PROD_A) OR Len(Trim(PROD_A))=0) AND PROD_B = ‘P’, 'N', 'P')

Hope it helps