topic Re: IsNull function behaviour in QlikView

IsNull function behaviour

konidena — Tue, 01 Mar 2016 16:14:49 GMT

Hi Team,

I am using IsNull function in my script.

I have columns called Region, SalesRep, Sales.

LOAD * INLINE [

Name, SalesRep, Sales

HYD, sravan, 2000

Pune, Madhu

Delhi, Srinivas, 3000

Banglore, Bhuvan, 15000

Chennai, Karthik, 17000

];

NoConcatenate

Load *,

If(IsNull(Sales),Sales,0) as NewSales

Resident Details;

Drop table Details;

Question: For the SalesRep Madhu, the sale is Null.

Isnull Function returning "0" in the column "NewSales" for madhu. But, it is not showing the sales values where the actual sales is happend.

How to get the Actual sales as well as 0 in the "NewSales" field.

Regards

Srinivas

Re: IsNull function behaviour

Anonymous — Tue, 01 Mar 2016 17:39:51 GMT

Try using this way:

If(IsNull(Sales),0,Sales) as NewSales

The IF sintax is CONDITION, TRUE, FALSE. The way that you put it, every time that there's a null value on Sales, you will write it, and when it's not null, you will write 0.

Regards,

Gabriel

Re: IsNull function behaviour

sunny_talwar — Tue, 01 Mar 2016 17:39:59 GMT

Try this instead:

NoConcatenate

Load *,

If(Len(Trim(Sales)) > 0, Sales, 0) as NewSales

Resident Details;

Re: IsNull function behaviour

maxgro — Tue, 01 Mar 2016 22:13:27 GMT

2 problems:

// 1) you have a blank or space in the Sales field for Madhu, not a null; if you want a null you can add the bold

Details:

LOAD Name, SalesRep, if(len(trim(Sales))=0, null(), Sales) as Sales INLINE [

Name, SalesRep, Sales

HYD, sravan, 2000

Pune, Madhu

Delhi, Srinivas, 3000

Banglore, Bhuvan, 15000

Chennai, Karthik, 17000

];

// 2) now you have a true null, you can check for null, replacing null with 0

Final:

NoConcatenate

Load

If(IsNull(Sales),0,Sales) as NewSales

Resident Details;

Drop table Details;

Re: IsNull function behaviour

MarcoWedel — Tue, 01 Mar 2016 22:47:39 GMT

Hi,

other solutions without temporary tables, resident loads, IsNull() and string operations could be:

tabDetails:

LOAD Name,

SalesRep,

RangeSum(Sales,0) as Sales

Inline [

Name, SalesRep, Sales

HYD, sravan, 2000

Pune, Madhu

Delhi, Srinivas, 3000

Banglore, Bhuvan, 15000

Chennai, Karthik, 17000

];

tabDetails:

LOAD Name,

SalesRep,

Alt(Sales*1,0) as Sales

Inline [

Name, SalesRep, Sales

HYD, sravan, 2000

Pune, Madhu

Delhi, Srinivas, 3000

Banglore, Bhuvan, 15000

Chennai, Karthik, 17000

];

hope this helps

regards

Marco

Re: IsNull function behaviour

Anonymous — Wed, 02 Mar 2016 05:29:18 GMT

try this one If(IsNull(Sales),0,Sales) as NewSales

Re: IsNull function behaviour

jonathandienst — Wed, 02 Mar 2016 09:51:06 GMT

You could use the Alt() function, which is designed for this problem:

Alt(Sales, 0) As NewSales,

Re: IsNull function behaviour

tyagishaila — Wed, 02 Mar 2016 09:56:24 GMT

Use only Sales in place of IsNull(Sales)

If(Sales,Sales,0) as NewSales

And

go to Presentation Tab of Table

Unclick Suppress Zero-Values.

Re: IsNull function behaviour

tyagishaila — Wed, 02 Mar 2016 10:08:58 GMT

for SalesRep 'Madhu' Sales is not NULL value it is a missing value.

Re: IsNull function behaviour

bohravanraj — Wed, 02 Mar 2016 10:20:06 GMT

PFA QVW with requested Sales Data.

Re: IsNull function behaviour

konidena — Wed, 02 Mar 2016 17:12:49 GMT

If(IsNull(Sales),0,Sales) as NewSales is not working. Still it is showing Null value instead of 0