Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

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
Valued Contributor

transform Null value to value

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

17 Replies
Not applicable

transform Null value to value

Try it:

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

Ciao!

Daniela

Not applicable

transform Null value to value

Unfortunately this won't work, any other ideas?

biester
Valued Contributor

transform Null value to value

Coalesce() will only work with MSSQL.

Try:

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

Rgds,
Joachim

Not applicable

AW:Re: transform Null value to value

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
Valued Contributor

AW:Re: transform Null value to value

@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

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

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

transform Null value to value

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
Valued Contributor

transform Null value to value

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

AW:Re: transform Null value to value

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;

Community Browser