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

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

Sun, 22 Jan 2012 17:52:43 GMT

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 deal	Length of Sales Deal Field	Count of Sales Deal
z	1	221
z	10	221

Example with actual data that did not have an empty field

Sales deal	Length of Sales Deal Field	Count of Sales Deal
0000023833	1	4
0000023833	10	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

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

Sun, 22 Jan 2012 19:14:45 GMT

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

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

rwunderlich — Mon, 23 Jan 2012 05:47:22 GMT

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