transform Null value to value

[#1]

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;

[#2]

Excuse me:

load

*,

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

sql select
production,

costcenter
from table1 ;

[#3]

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?

[biester]

Try:

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

Rgds,
Joachim

[#5]

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.

[biester]

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

[#7]

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";

[biester · Accepted solution]

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

[#9]

Yes, thx Joachim!

IT WORKS