topic Re: IF() returning NULL for the ELSE in QlikView

IF() returning NULL for the ELSE

Thu, 19 Jun 2014 14:45:02 GMT

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.

Re: IF() returning NULL for the ELSE

joshabbott — Thu, 19 Jun 2014 15:00:51 GMT

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

Re: IF() returning NULL for the ELSE

swuehl — Thu, 19 Jun 2014 15:06:33 GMT

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;

Re: IF() returning NULL for the ELSE

Thu, 19 Jun 2014 15:12:32 GMT

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

Re: IF() returning NULL for the ELSE

Thu, 19 Jun 2014 15:17:52 GMT

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.

Re: IF() returning NULL for the ELSE

antoniotiman — Thu, 19 Jun 2014 15:21:49 GMT

Hi David,

what is the origin of your data ? perhaps MySQL ?

Re: IF() returning NULL for the ELSE

Thu, 19 Jun 2014 15:25:11 GMT

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.

Re: IF() returning NULL for the ELSE

PrashantSangle — Thu, 19 Jun 2014 15:27:31 GMT

Hi,

Can you share screen shot of your table box

Or It is best if you share your application

Regards,

Re: IF() returning NULL for the ELSE

antoniotiman — Thu, 19 Jun 2014 15:33:21 GMT

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

Re: IF() returning NULL for the ELSE

Thu, 19 Jun 2014 22:42:43 GMT

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.

Re: IF() returning NULL for the ELSE

swuehl — Fri, 20 Jun 2014 11:09:01 GMT

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

Re: IF() returning NULL for the ELSE

Fri, 20 Jun 2014 13:08:17 GMT

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.

Re: IF() returning NULL for the ELSE

antoniotiman — Fri, 20 Jun 2014 14:11:34 GMT

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