IF() returning NULL for the ELSE

[#1]

I'm perplexed as to why I can't seem to get an ELSE value from my IF() function.  I can't imagine it is just because I'm using a preceding load.

LOAD *

  ,IF (LEN(Trim(attribute_label)) > 0, 'True', 'False') AS has_attribute;

SELECT

ad_id

,attribute_label

,etc......

When I put the result in the table box I'll get the 'True' value in my has_attribute field but where I should have 'False' I get nothing but NULLs.

I must be overlooking something obvious but I'm at wit's end with this seemingly simple task and also can't seem to figure out the proper search terms to find a solution on the forums.

[swuehl]

Antonio,

thanks for showing that issue with MySQL.

I haven't used MySQL much with QV, and even after a short search, I haven't understood how this MySQL feature should break the preceding load in QV.

It seems it should only effect either storing data into the database or when comparing string values by the DBMS.

Just for interest and without doubting that there obviously is an issue, could you add some more information or a link about Auto Trim feature and maybe your assumption why this causes the issue?

So maybe there is a better workaround in QV than creating the QVDs etc.

Regards,

Stefan

[#3]

I second this notion.  I've found a few articles about 'Auto Trim' on these forums but still not quite sure what is going on much less how you fixed it.  An explanation would also make a nice addendum to this technical brief NULL handling in QlikView which I was pouring over trying to figure out this odd behavior. 

[antoniotiman]

Hi swuehl,

I try to explain the behavior of MySQL.

With fields  char/varchar (not with text and blob)

if insert blank in field the data is stored as chr(0).

In QV this field is NOT NULL, NOT BLANK and len(Field)=0.

However  to resolve the issue I use in the script :

Table:

SQl Select

field1,field2,

case

     when Fieldblank = ' ' then ' '     /* blank forced */

     else Fieldblank

as Fieldblank,

field4,

...

from

In this case the Filedblank in QV is  ' ' (1 blank)  and len(Fieldblank)=1.

For this reason You can't use PRELOAD and LOAD but, after this script,

You can use Load ... Resident and You store correct QVD.

Regards,

Antonio