Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
konidena
Creator
Creator

IsNull function behaviour

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

1 Solution

Accepted Solutions
MarcoWedel

Hi,

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

QlikCommunity_Thread_207650_Pic1.JPG

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

];

or

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

View solution in original post

10 Replies
Anonymous
Not applicable

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

sunny_talwar

Try this instead:

NoConcatenate

Load *,

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

Resident Details;

maxgro
MVP
MVP

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;


1.png

MarcoWedel

Hi,

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

QlikCommunity_Thread_207650_Pic1.JPG

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

];

or

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

Anonymous
Not applicable

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

jonathandienst
Partner - Champion III
Partner - Champion III

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

     Alt(Sales, 0) As NewSales,

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
tyagishaila
Specialist
Specialist

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.

tyagishaila
Specialist
Specialist

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

bohravanraj
Partner - Creator II
Partner - Creator II

PFA QVW with requested Sales Data.