topic Replacing null value in QlikView

Replacing null value

tripatirao — Thu, 01 Oct 2015 11:10:37 GMT

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....

Re: Replacing null value

ManuelRühl — Thu, 01 Oct 2015 11:12:07 GMT

if(isnull(Brandname),'OtherBrand',Brandname) as Brandname,

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

Re: Replacing null value

tripatirao — Thu, 01 Oct 2015 11:14:08 GMT

I tried this but it is not working for me.

Re: Replacing null value

ManuelRühl — Thu, 01 Oct 2015 11:15:58 GMT

is it really null() or is there anything in the field ?

Re: Replacing null value

gautik92 — Thu, 01 Oct 2015 11:18:23 GMT

if(IsNull( Brand ),'OtherBrand',Brand)

this syntax is working fine for me

Re: Replacing null value

pamaxeed — Thu, 01 Oct 2015 11:19:24 GMT

you have there a null value or an empty string or '-' ?
It is depending on it....

Re: Replacing null value

prma7799 — Thu, 01 Oct 2015 11:19:50 GMT

Please try this.

ABC:

LOAD *,

if(Brandname ='-','OtherBrand',Brandname)as Brandname1;

LOAD * Inline

[

Brandname ,Sales

abc,1000

pqr,2000

-,2500

xyz,1500

];

Thanks

Re: Replacing null value

gautik92 — Thu, 01 Oct 2015 11:20:50 GMT

try qlikview7799 suggestion it will work for sure

Re: Replacing null value

maxgro — Thu, 01 Oct 2015 11:22:25 GMT

where do you get null for BrandName?

in a table? Brandname and Sales are fields of the same table?

in a chart?

Re: Replacing null value

tripatirao — Thu, 01 Oct 2015 11:29:00 GMT

in straight table the null value is replaced by OtherBrand in following way by taking

calculated dimesion :if(len(trim(BrandName))=0,'OtherBrand',BrandName)

expression:sum(Sales)

But when i am doing in script blank space is showing.

Re: Replacing null value

Thu, 01 Oct 2015 11:29:40 GMT

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

Re: Replacing null value

prma7799 — Thu, 01 Oct 2015 11:31:38 GMT

please share some sample data.

Re: Replacing null value

tripatirao — Thu, 01 Oct 2015 11:49:45 GMT

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.

Re: Replacing null value

gautik92 — Thu, 01 Oct 2015 11:52:01 GMT

values are null or '-'??

Re: Replacing null value

tripatirao — Thu, 01 Oct 2015 13:51:18 GMT

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