topic Re: Null Help in QlikView

Null Help

bhavvibudagam — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

Can any one please help me on below requirement.

I have two fields called PRODUCT1 and PRODUCT2 contains blank and null values.

I have convert these values into Null Text.

I have tried

NULLASVALUE "PRODUCT1","PRODUCT2";

SET NULLVALUE = 'NULL';

But i am not able to see NULL text in the above fields.

Please help me on this.

Thanks in advance.

Re: Null Help

shiveshsingh — Thu, 10 May 2018 07:27:44 GMT

Try this

Set Nullinterpret = 'NULL';

NULLASVALUE "PRODUCT1","PRODUCT2";

Set NullValue = 'NULL';

Re: Null Help

bhavvibudagam — Thu, 10 May 2018 08:05:25 GMT

Hi,

Still i am not able to see NULL text in these fields.

Re: Null Help

jyothish8807 — Thu, 10 May 2018 08:06:10 GMT

Hi,

Try in script like this:

Table:

Load *,

if(Len(trim(Product1))>0, Product1,null()) as Product1,

if(Len(trim(Product2))>0, Product2,null()) as Product1

resident <>

Br,

Re: Null Help

Anil_Babu_Samineni — Thu, 10 May 2018 08:33:34 GMT

May be delete Double Quotes? and then try using below

NULLASVALUE PRODUCT1,PRODUCT2;

SET NULLVALUE = 'NULL';

Re: Null Help

dineshm030 — Thu, 10 May 2018 08:36:28 GMT

Try this:

If(Product1 = ' ', 'NULL',

If(Product2 = '-','NULL'));

Re: Null Help

jyothish8807 — Thu, 10 May 2018 08:40:21 GMT

Try Like this:

T1:

Load

if(Len(trim(Product1))>0, Product1,'NULL') as Product1,

if(Len(trim(Product2))>0, Product2,'NULL') as Product2;

Sql select

p.product1,

p.product2

from product p;

Noconcatenate

T2:

Load *

resident T1

where Product1='NULL' and Product2 = 'NULL';

drop table T1;

Re: Null Help

shan_cse2003 — Thu, 10 May 2018 08:57:06 GMT

Hi Bhavani,

You don't need to do anything in script, just go the expression tab and use the following expression:

If (Sum(Product1)=0,'NULL',

Num(Sum(Product1),'###'))

Please find the sample application for your reference. Hope this will work for you.

Re: Null Help

jyothish8807 — Thu, 10 May 2018 10:20:16 GMT

something like this may be:

select product1
, case when product1 is null then 'NULL' else product1 end
from table;

Re: Null Help

jyothish8807 — Thu, 10 May 2018 10:22:54 GMT

Or may be this:

select product1
, nvl(product1, ‘NULL’)

from table;

Re: Null Help

sasiparupudi1 — Thu, 10 May 2018 10:37:28 GMT

Load

DATE,

PRODUCT1,

PRODUCT2;

Sql select

p.date,

COALESCE(p.product1,'NULL') AS p.product1

COALESCE(p.product2,'NULL') AS p.product2

from product p where to_Char(p.date,'DD/MM/YYYY') in $(WHEREPART);