Replacing null value

[tripatirao]

Hi all

I want to replace Brandname  as OtherBrand where Brandname is null .

I want to do  this  in the script.

for exemple:

Brandname  ,Sales

abc,1000

pqr,2000

-,2500

xyz,1500

when the scrpt will load my Brandname dimension should be like below.

Brandname

abc,

pqr,

OtherBrand,

xyz

Thanks in advance....

[#2]

try out

IF(len(trim(BrandName)) = 0, 'OtherBrand', BrandName) as BrandName1

this should do the job.

isNull() is not always working as you might think, because a "empty" field is not always NULL. I could be space, tab or whatever.

trim() will eliminate all space, tabs etc. an len gives you the lengths afterwards, if this is 0, then you can say for sure, its a null value in there (i thinks there is no brandname which is callde "space" --> " "

[prma7799]

please share some sample data.

[tripatirao]

I am working for a pharmaceutical data.

I  have loaded  data from excel file.

there are so many field are present.

brandname,unitprice,shipquantity, prodctiontype.

abc,1000,20,tablet

xyz,2000,10,suspension

pqr,3000,30,capsule

blankspace,2000,40,injection

In excel file only for brandname some values are empty.

[gautik92]

values are null or '-'??

[tripatirao]

Hi Gautam

Thanks for your time.Actually that is a empty field in excel.

The problem is solved.

I forget to use the second expression  what manual ruhi suggested.

if(len(Brandname)>0,Brandname,'OtherBrand') as Brandname,

Thanks