topic transform Null value to value in QlikView

transform Null value to value

Mon, 26 Jan 2026 18:19:17 GMT

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;

transform Null value to value

Wed, 14 Oct 2009 16:05:26 GMT

Try it:

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

Ciao!

Daniela

transform Null value to value

Wed, 14 Oct 2009 16:26:36 GMT

Unfortunately this won't work, any other ideas?

transform Null value to value

biester — Wed, 14 Oct 2009 17:21:27 GMT

Coalesce() will only work with MSSQL.

Try:

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

Rgds,
Joachim

AW:Re: transform Null value to value

Wed, 14 Oct 2009 17:38:10 GMT

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

AW:Re: transform Null value to value

biester — Wed, 14 Oct 2009 17:56:13 GMT

@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

AW:Re: AW:Re: transform Null value to value

Wed, 14 Oct 2009 18:21:09 GMT

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

transform Null value to value

Wed, 14 Oct 2009 18:28:45 GMT

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.

transform Null value to value

biester — Wed, 14 Oct 2009 18:34:32 GMT

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

AW:Re: transform Null value to value

Wed, 14 Oct 2009 18:39:46 GMT

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;

AW:Re: transform Null value to value

Wed, 14 Oct 2009 18:43:36 GMT

Excuse me:

load

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

sql select
production,

costcenter
from table1 ;

transform Null value to value

Wed, 14 Oct 2009 20:03:28 GMT

Yes, sorry

It isn't NULL but a it's just an empty string. What do I have to change in the script to make it work?

transform Null value to value

biester — Wed, 14 Oct 2009 20:14:30 GMT

Try:

if( len( trim( costcenter ) ) = 0,'4000',costcenter) ...

Rgds,
Joachim

transform Null value to value

Wed, 14 Oct 2009 21:13:39 GMT

No change. I still have no production on costcenter 4000. It's stil all on the costcenter with no value (empty). Verry strange.... I cannot find the solution.

transform Null value to value

biester — Wed, 14 Oct 2009 23:26:12 GMT

Can you post the entire script portion regarding this problem? I think the error is NOT that empty costcenters are not converted to 4000 in the first step. In your first example at least you make a table only with costcenter1 in the first load - no empty costcenter1 values should be there any more in that step; in the following "select production, costcenter as costcenter1 ..." you are loading the empty costcenter - values again from the database of course and rename it to costcenter1 and create another table - then the two tables are joined by costcenter1; second table HAS empty values, first table has NOT. Hence, the empty values from table2 have production.

Rgds,
Joachim

transform Null value to value

Fri, 16 Oct 2009 14:48:20 GMT

Yes ofcourse. (sorry for the late respons)

Here is the script:

LOAD if( len( trim( costcenter ) ) = '0','4000',costcenter) as costcenterabroad;

sql select *

FROM "DataBI_Versie4".dbo."PCD_Afdelingen";

sql select "Afd_Extra1",

"Afd_Extra2",

"Afd_Extra3",

"Afd_ID",

"Inst_ID",

costcenter as costcenterabroad,

LISZNumber,

Name

FROM "DataBI_Versie4".dbo."PCD_Afdelingen";

transform Null value to value

biester — Fri, 16 Oct 2009 15:59:26 GMT

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

transform Null value to value

Tue, 20 Oct 2009 16:06:38 GMT

Yes, thx Joachim!

IT WORKS