Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

transform Null value to value

Got a problem. Want to give al empty values in the field costcenter the value 4000.

My query won't work. What's wrong? I still got empty values with production on it.

Load if(costcenter ='', '4000',costcenter) as costcenter1;
sql select *
from table1;

sql select
production,
costcenter as costcenter1
from table1;

1 Solution

Accepted Solutions
biester
Specialist
Specialist

Try the following (I tried it in a test environment and it worked):

1. in the first line you must write " = 0 " ionstead of " = '0' . You compare to an integer and not to a string.

2. AFTER the first "FROM" and the BEFORE the second "sql select" (would be between line 3 and 4) insert:

LOAD
"Afd_Extra1",
"Afd_Extra2",
"Afd_Extra3",
"Afd_ID",
"Inst_ID",
if( len ( trim ( costcenterabroad ) ) = 0,'4000',costcenterabroad ) as costcenterabroad,
LISZNumber,
Name;

If that doesn't help, I'm out of ideas.

Rgds,
Joachim

View solution in original post

17 Replies
Not applicable
Author

Try it:

sql select
production,
Coalesce(costcenter,'') as costcenter1
from table1;

Ciao!

Daniela

Not applicable
Author

Unfortunately this won't work, any other ideas?

biester
Specialist
Specialist

Coalesce() will only work with MSSQL.

Try:

load if(Isnull(costcenter),'4000',costcenter)

Rgds,
Joachim

Not applicable
Author

Be aware of different null value handling on 32 and 64 Bit QV Systems!

Load ....

if (isnull(costcenter), '4000', if(costcenter = '', '4000', costcenter) as costcenter

;....

Greedings Boris

biester
Specialist
Specialist

@Boris,

what do you have in mind? In how far is there a difference? I tested my example both on a 32bit machine with 32bit QV.exe and a 64bit machine with 64bit Qv.exe, both with OLEDB and ODBC (connecting to a MSSQL), and in all of these scenarios the simple if(isnull(fld),'N/A',fld) as fld worked.

Rgds,
Joachim

Not applicable
Author

This information is taken from the wiki:

http://community.qlik.com/wikis/qlikview-wiki/isnull-difference-in-qv-64bit-and-32bit.aspx

I never ran personally into this, but I keept it in mind as a possible source of missbehaviour...

Greeding Boris

Not applicable
Author

I've tried

load if(Isnull(costcenter),'4000',costcenter) as costcenterabroad
from table1 ;

sql select

production,
costcenter as costcenterabroad
from table1 ;


Unfortunately it does not work .I still get no production for costcenter 4000 but when I select the empty field, there is a lot of production on it. So the empty costcenter still apears with production data instead of shifting it to 4000.

biester
Specialist
Specialist

Could it be that, as in your case if isnull() does not work, the value simply isn't NULL and it's just an empty string? An empty and NULL are of course two different things.

Rgds,
Joachim

Not applicable
Author

As this is a proceeding load script get rid of the "form table1" and add the columns you want to reside... in the new table (production)

load

production,

if(Isnull(costcenter),'4000',costcenter) as costcenterabroad;

sql select
production,
costcenter as costcenterabroad
from table1 ;



Greedings Boris;