topic Null, Empty or Blank in QlikView

Null, Empty or Blank

Tue, 26 Apr 2011 23:02:37 GMT

I have a field in a table that I need a WHERE statement on. The field is rejected order lines and will have code in the field if the line has been rejected. If the line has not been rejected it is null. However, if a Customer Service Rep(CSR) rejects the line (puts in a code) and then changes their mind and "blanks" the field, it is not NULL any more it is Empty or Blank. Sometime the CSR goes into the field and puts in a space to remove the rejected order code.

I need to see only items that have not been rejected. I currently have to using the following:

WHERE MyField like '' OR MyFiled like ' ' OR MyField like ' ' OR ISNULL(MyField);

I have also used :

WHERE len(MyField) < 2; but the code (currently only two characters) could eventually go to a three character code or four or . . . ..

Is there a better way to test the field to trap that there is "no code" in field.

Thanks,
Stephen

Null, Empty or Blank

erichshiino — Tue, 26 Apr 2011 23:05:35 GMT

try:

where len(trim(MyField))>0

it will remove spaces then count characters

Rgds,

Null, Empty or Blank

Tue, 26 Apr 2011 23:45:57 GMT

That gives me everything "with a code" . I need everthing thing with out a code.

Stephen

Null, Empty or Blank

Wed, 27 Apr 2011 00:04:48 GMT

Using:

WHERE NOT len(trim(MyField)) >= 2

seems to work.

Thanks,

Stephen

Null, Empty or Blank

Mon, 15 Aug 2011 13:53:30 GMT

An old post, but if you still need an answer you can use PurgeChar(text, remove_char) (in script load) to remove unwanted characters, that includes spaces.

Carl