Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

IF() returning NULL for the ELSE

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.

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

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 .....

View solution in original post

12 Replies
joshabbott
Creator III
Creator III

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
MVP
MVP

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;

Not applicable
Author

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

Not applicable
Author

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
Master III
Master III

Hi David,

what is the origin of your data ? perhaps MySQL ?

Not applicable
Author

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,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
antoniotiman
Master III
Master III

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 .....

Not applicable
Author

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.