Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi experts,
I am stuck with a load issue that I want to convert all empty values for one dimension into something else like a # at load script section.
what is the code logistic ?
let 's say here is my loading script, in which "contract #" is the dimension contains empty value that I want to replace "#"
Contract_Sales:
LOAD
"Contract #",
Cus,
Group
From blah blah....
Thanks
@coloful_architect try below
Contract_Sales:
LOAD
if( len(trim( "Contract #"))=0,'#',"Contract #") as "Contract #",
Cus,
Group
From blah blah....
Maybe like this?
LOAD
If(len([Contract #] ) =0,'#' ,[Contract #] ) as [Contract #]
Cus,
Group
From...
Perhaps
Contract_Sales:
LOAD
'#' as "Contract #",
Cus,
Group
From blah blah....
I want to clarify a bit.
"Contract #" is a dimension/ a field that includes both empty value and non-empty values.
I want to replace all those empty value with "#" and remain those non-empty values as the same.
@coloful_architect try below
Contract_Sales:
LOAD
if( len(trim( "Contract #"))=0,'#',"Contract #") as "Contract #",
Cus,
Group
From blah blah....
Maybe like this?
LOAD
If(len([Contract #] ) =0,'#' ,[Contract #] ) as [Contract #]
Cus,
Group
From...
If your field only holds numeric values then you could simplify my previous suggestion by using the Alt() function. Like this.
LOAD
Alt([Contract #] ,'#' ) as [Contract #]
Cus,
Group
From....
If your empty values are loaded as NULL values in the script then there is another solution to your issue. You can handle the null value straight in the script. Like this
NullAsValue [Contract #] ;
Set NullValue = '#';
LOAD
[Contract #],
Cus,
Group
From....
Read more on null handling in the links below