Discussion Board for collaboration on QlikView Scripting.
I have a table with 2 fields, field1 and field 2. Field 1 has 100 records, field 2 only has 75 records. When field 2 is null, I want to assign a value to it but the 25 records without data show up with what appears to be a null value. I have tried several things. 1st, when I do a len(field2) all of them show up as 10, even if they do not have data in them. I have tried doing a trim on field 2 but it deletes the entire line leaving me with 75 records not 100.
How do I deal with a field that has no data in it, but has a length of 10? I want the script to be, if Field2 is Null, assign Field1 as its value, else Field2.
during the load script can you check for this blank field and use the Null() to set it to a null value?
What is actually in the field that you are loading?
Is it a CSV and have you used an Editor to check the value in that field?
The earlier if test will perform incorrectly when your "empty" field contains blanks as yours probably does.
If(trim(len(field2)) = 0, Value, field2) AS field2
Will always return field2 AS field2 for you since len(field2) = 10 and trimming it has no effect.
The correct form is:
if(len(trim(field2)) = 0, value, field2) AS field2
Trim will trim away all blanks leaving a zero length field2 value and field2 will be filled by "value".
If those 25 values in field2 are not blanks but some other non-printable characters, this won't work but it is unlikely these values aren't 10 blanks.