How to show NULL values with IF function in script

[qlikviewwizard]

Hi All,

How to show NULL values with IF function in script?

Thanks in advance.

[Anonymous]

Do you mean isnull()?

BR

Serhan

[CELAMBARASAN]

Could you please explain little more clear about your question?

Celambarasan

[qlikviewwizard]

Hi Please check the below script.

There is no SAL for SMITH and MARTIN. But I want to show in report.

Thanks in advance.

EMP:


LOAD EMPNO,ENAME,JOB,date(DATE) as DATE,Ceil(month(date(DATE))/3) as Quarter,SAL Inline [


EMPNO,ENAME,JOB,DATE,SAL
7369,SMITH,CLERK,1-Jan-15,
7369,SMITH,CLERK,1-Apr-15,
7499,ALLEN,SALMAN,1-Jan-15,4000
7499,ALLEN,SALMAN,1-Apr-15,5000
7521,WARD,SALMAN,1-Jan-15,6000
7521,WARD,SALMAN,1-Apr-15,1500
7566,JONES,MANAGER,1-Jan-15,2500
7566,JONES,MANAGER,1-Apr-15,1100
7654,MARTIN,SALMAN,1-Jan-15,
7654,MARTIN,SALMAN,1-Apr-15,
7698,BLAKE,MANAGER,1-Jan-15,3450
7698,BLAKE,MANAGER,1-Apr-15,4000
7782,CLARK,MANAGER,1-Jan-15,4000
7782,CLARK,MANAGER,1-Apr-15,5000
7788,SCOTT,ANALYST,1-Jan-15,6000
7788,SCOTT,ANALYST,1-Apr-15,1500
7839,KING,PRESIDENT,1-Jan-15,3600
7839,KING,PRESIDENT,1-Apr-15,3450
7844,TURNER,SALMAN,1-Jan-15,1250
7844,TURNER,SALMAN,1-Apr-15,4000
7876,ADAMS,CLERK,1-Jan-15,5000
7876,ADAMS,CLERK,1-Apr-15,6000
7900,JAMES,CLERK,1-Jan-15,3450
7900,JAMES,CLERK,1-Apr-15,4000
7902,FORD,ANALYST,1-Jan-15,4000
7902,FORD,ANALYST,1-Apr-15,5000
7934,MILLER,CLERK,1-Jan-15,6000
7934,MILLER,CLERK,1-Apr-15,6000
];

[qlikviewwizard]

darkhorse‌

I don't which function need to be used.

[maleksafa]

go to the chart properties, the presentation and un-check the suppress zero-Values

[Kushal_Chawda · Accepted solution]

Please find the attached.

[MayilVahanan]

Hi

You can try like this

Uncheck the suppress "0" values in "Presentation" tab.

In script, If(ISNULL(SAL), 0, SAL) AS SAL

[PrashantSangle]

Hi,

try below

EMP:

LOAD EMPNO,ENAME,JOB,date(DATE) as DATE,Ceil(month(date(DATE))/3) as Quarter,SAL Inline [

EMPNO,ENAME,JOB,DATE,SAL
7369,SMITH,CLERK,1-Jan-15,
7369,SMITH,CLERK,1-Apr-15,
7499,ALLEN,SALMAN,1-Jan-15,4000
7499,ALLEN,SALMAN,1-Apr-15,5000
7521,WARD,SALMAN,1-Jan-15,6000
7521,WARD,SALMAN,1-Apr-15,1500
7566,JONES,MANAGER,1-Jan-15,2500
7566,JONES,MANAGER,1-Apr-15,1100
7654,MARTIN,SALMAN,1-Jan-15,
7654,MARTIN,SALMAN,1-Apr-15,
7698,BLAKE,MANAGER,1-Jan-15,3450
7698,BLAKE,MANAGER,1-Apr-15,4000
7782,CLARK,MANAGER,1-Jan-15,4000
7782,CLARK,MANAGER,1-Apr-15,5000
7788,SCOTT,ANALYST,1-Jan-15,6000
7788,SCOTT,ANALYST,1-Apr-15,1500
7839,KING,PRESIDENT,1-Jan-15,3600
7839,KING,PRESIDENT,1-Apr-15,3450
7844,TURNER,SALMAN,1-Jan-15,1250
7844,TURNER,SALMAN,1-Apr-15,4000
7876,ADAMS,CLERK,1-Jan-15,5000
7876,ADAMS,CLERK,1-Apr-15,6000
7900,JAMES,CLERK,1-Jan-15,3450
7900,JAMES,CLERK,1-Apr-15,4000
7902,FORD,ANALYST,1-Jan-15,4000
7902,FORD,ANALYST,1-Apr-15,5000
7934,MILLER,CLERK,1-Jan-15,6000
7934,MILLER,CLERK,1-Apr-15,6000
];

Left Join
Load EMPNO,
ENAME,
JOB,
DATE,
if(isnull(Sum(SAL)) or len(trim(Sum(SAL)))=0 or Sum(SAL)=0,50,sum(SAL)) as New_Sales
Resident EMP
group by EMPNO,ENAME,JOB,DATE

EMP:

EMPNO,

ENAME,

JOB,

DATE,SAL

from tableName

Left Join

Load EMPNO,

ENAME,

JOB,

DATE,

if(isnull(Sum(Sales)) or len(trim(Sum(Sales)))=0 or Sum(Sales)=0,yourValue,sum(Sales) as New_Sales

Resident EMP

group by EMPNO,ENAME,JOB,DATE

Regards

[qlikviewwizard]

Thank you all for the answer.