topic Re: Null Value recognition in syntax Expression in QlikView

Null Value recognition in syntax Expression

Fri, 19 Oct 2012 16:05:43 GMT

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.

Re: Null Value recognition in syntax Expression

MayilVahanan — Fri, 19 Oct 2012 16:24:41 GMT

Try like this

isNull(FieldName) or

len(Trim(FieldName))=0

FieldName =''

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

Hope it helps

Re: Null Value recognition in syntax Expression

CELAMBARASAN — Fri, 19 Oct 2012 16:42:20 GMT

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