topic Re: How to show NULL values with IF function in script in QlikView

How to show NULL values with IF function in script

qlikviewwizard — Tue, 21 Jul 2015 06:45:19 GMT

Hi All,

How to show NULL values with IF function in script?

Thanks in advance.

Re: How to show NULL values with IF function in script

Anonymous — Tue, 21 Jul 2015 06:48:07 GMT

Do you mean isnull()?

Serhan

Re: How to show NULL values with IF function in script

CELAMBARASAN — Tue, 21 Jul 2015 06:55:00 GMT

Could you please explain little more clear about your question?

Celambarasan

Re: How to show NULL values with IF function in script

qlikviewwizard — Tue, 21 Jul 2015 07:28:48 GMT

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

];

Re: How to show NULL values with IF function in script

qlikviewwizard — Tue, 21 Jul 2015 07:30:59 GMT

darkhorse‌

I don't which function need to be used.

Re: How to show NULL values with IF function in script

maleksafa — Tue, 21 Jul 2015 07:31:50 GMT

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

Re: How to show NULL values with IF function in script

Kushal_Chawda — Tue, 21 Jul 2015 07:32:12 GMT

Please find the attached.

Re: How to show NULL values with IF function in script

MayilVahanan — Tue, 21 Jul 2015 07:36:08 GMT

You can try like this

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

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

Re: How to show NULL values with IF function in script

PrashantSangle — Tue, 21 Jul 2015 07:42:55 GMT

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

Re: How to show NULL values with IF function in script

qlikviewwizard — Tue, 21 Jul 2015 09:08:52 GMT

Thank you all for the answer.

Re: How to show NULL values with IF function in script

jagan — Wed, 22 Jul 2015 10:13:41 GMT

Hi,

Use Len() & Trim() it covers both Null and Empty values.

LOAD

If(Len(Trim(SAL)), 0, SAL) AS SAL

FROM DataSource;

Hope this helps you.

Regards,

jagan.

Re: How to show NULL values with IF function in script

qlikviewwizard — Wed, 22 Jul 2015 10:23:19 GMT

Thank you jagan