Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Field Length Issue - Anyone ever see this before?????

Has anyone ever seen this, I have a Char field that has a length of 10 char.  In the data, the field would appear empty and I wanted to add a 'z' to it if it was empty. I had issues with trying to trim this field, using isnull, and then I had to finally do a purge char which got rid of the majority of them; Now I have this,

Example with the Z added:

Sales   dealLength of Sales   Deal FieldCount of Sales   Deal
z1221
z10

221

Example with actual data that did not have an empty field

Sales   dealLength of Sales   Deal FieldCount of Sales   Deal
000002383314
000002383310

18

How could this sales deal field have a length of 1 and a length of 10 with the same value in this field?  I want to be able to delete all records where the sales deal = z and doing an inner join where it's <> 'z' is not working.  It did earlier but I am still left with 221 of them.  ANy thoughts?Thanks, Sean

2 Replies
Not applicable

Field Length Issue - Anyone ever see this before?????

Have you tried with

isnull(field) or field=null() or len(trim(field)) = 0 (or trim(field) = '')  

?

of course 3 and 4 are the same. Remember that null() and isnull(field) is not the same.

The actual lenght of the field is not working properly, but maybe you need to post the script and expression.

Regards

rwunderlich
Not applicable

Re: Field Length Issue - Anyone ever see this before?????

A database char field can contain blanks which is not the same as database null. Additionally, there have been issues with consistency of IsNull() on the 64bit platform. There are also differences between blank trimming on database reads and file reads (controlled by Verbatim= variable). I've found the most reliable way to detect "empty" fields for all cases is

len(trim(field))=0

Although I will admit that your "z" example looks strange. I  debug these cases with an listbox expression of

='>' & field & '<'

What expression are you using for your "Length of Sales Deal Field" field? Is that a calculated dimension or an expression?

-Rob