Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
Try it:
sql select
production,
Coalesce(costcenter,'') as costcenter1
from table1;
Ciao!
Daniela
Unfortunately this won't work, any other ideas?
Coalesce() will only work with MSSQL.
Try:
load if(Isnull(costcenter),'4000',costcenter)
Rgds,
Joachim
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
@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
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
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.
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
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;