Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Read about Qlik’s Response to COVID-19 Read the Letter, Join the Group.
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.

Tags (3)
1 Solution

Accepted Solutions
Highlighted
Honored Contributor III

Re: IF() returning NULL for the ELSE

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
Highlighted
Contributor III

Re: IF() returning NULL for the ELSE

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

Highlighted
MVP
MVP

Re: IF() returning NULL for the ELSE

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;

Highlighted
Not applicable

Re: IF() returning NULL for the ELSE

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

Highlighted
Not applicable

Re: IF() returning NULL for the ELSE

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.

Highlighted
Honored Contributor III

Re: IF() returning NULL for the ELSE

Hi David,

what is the origin of your data ? perhaps MySQL ?

Highlighted
Not applicable

Re: IF() returning NULL for the ELSE

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.

Highlighted

Re: IF() returning NULL for the ELSE

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.
Highlighted
Honored Contributor III

Re: IF() returning NULL for the ELSE

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

Highlighted
Not applicable

Re: IF() returning NULL for the ELSE

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.