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.

[joshabbott]

I actually had better luck with this in a test:

LOAD *

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

SELECT

ad_id

,attribute_label

,etc......

[swuehl]

Your expression seems ok to me and should work as far as I see.

Just to eliminate any weird text interpretation issues:

  ,IF (LEN(Trim(attribute_label)) > 0, true(), false() ) AS has_attribute;

[#4]

Had high hopes for this as I did not know about the Alt() function but alas, I'm getting the same results.

[#5]

I agree, swuehl, there's nothing terribly exotic about what I'm trying to do.  I had tried your suggestion in an earlier attempt.  I also tried to combine it with Josh's answer but again, same results except I get "-1" instead of "True".

I'm going to put together a sample app in hopes I can duplicate this odd behavior.

[antoniotiman]

Hi David,

what is the origin of your data ? perhaps MySQL ?

[#7]

Yes.  In fact this is the first time I've had to make a substantial pull from MySQL.  I'm usually pulling from Postgres and the only other time I pulled from MySQL prior to this project was a single table.

[PrashantSangle]

Hi,

Can you share screen shot of your table box

Or It is best if you share your application

Regards,

[antoniotiman · Accepted solution]

This is a behavior of MySQL. It is 'Auto Trim'.

You can't use Preload.

Try

ID,

attribute_label,

case

     when attribute_label = ' ' then ' '   /* ' ' is 1 blank */

     else attribute_label

end

as has_attribute,

.....

from .....

[#10]

Sorry for the late reply but it still took quite some while to get this working correctly.  However, your advice took me down the right path and I am most grateful.

I also had to push my data to a QVD, remove all existing tables, pull it back in and only then could I finally apply the " ,IF (LEN(Trim(attribute_label)) > 0, 'True', 'False') AS has_attribute; " portion of the code and get the results I was looking for.  Your solution, or at least the way I applied it, resulted in every 'False' value being duplicated - one row showing 'False' and an additional one with the NULL marker.  Very odd and maddening.

Thank you everyone for your help.  I'm going still going to post the QVW after I shrink it a bit.