topic Null vs. Not Null in QlikView

Null vs. Not Null

tmumaw — Fri, 01 Oct 2010 14:42:53 GMT

Good Morning Everyone,

I am trying to populate Accq_Inv_Gross with Inv Gross if Customer Group 4 is not null, and populate Davey_Inv_Gross with Inv_Gross if Customer Group 4 is Null, however my logic must not be correct. Any suggestions?

Thanks

If(len([Customer Group 4]) > 0, [Inv Gross],0) as [Accq_Inv_Gross],

if(isnull([Customer Group 4]),[Inv Gross],0) as [Davey_Inv_Gross],

Null vs. Not Null

jonathandienst — Fri, 01 Oct 2010 14:55:53 GMT

Thom

I dont see anything wrong with your logic. What is happening that is not correct?

Jonathan

PS I would use len([Customer Group 4])=0 rather than isnull, but that wont change the results

Null vs. Not Null

Fri, 01 Oct 2010 15:53:52 GMT

Thom

I agree with Jonathan, that the logic is OK.

Check to see if the actual value you are checking are really null.

If you load numbers from Excel tables, QV will translate nulls on the sheet into "" in QV (blank string rather than a true null).

Null vs. Not Null

tmumaw — Fri, 01 Oct 2010 18:00:57 GMT

Thanks. It works fine. I changed the isnull to length = 0. I have another question. I want to do a count distinct customers. I tried this but with no luck. I'm having a hard time with the Great Than sign since set analysis also uses it.

Count({$<(len([Customer Group 4]) > 0>} distinct Customer

Thanks

Null vs. Not Null

Fri, 01 Oct 2010 18:17:37 GMT

Hi, try this:

Count({$< [Customer Group 4] = {"=len([Customer Group 4]) > 0"} > } distinct Customer)

Best Regards

Null vs. Not Null

tmumaw — Fri, 01 Oct 2010 18:24:58 GMT

Ivan,

You have just made my Friday and weekend. Thanks for all your help yet again.

Thom

Null vs. Not Null

tmumaw — Fri, 01 Oct 2010 18:25:52 GMT

Jonathan and Bruce,

You both also made my Friday much more enjoyable. Have a great weekend.

Thom