Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Arjunarao
Honored Contributor II

NULLINTERPRET

Hi All,

Please help to provide an example for the SET NULLINTERPRET=<sym>;

I enclosed the sample data.

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: NULLINTERPRET

Hi,

Check this from Qlikview help file, this applicable for only Text, Excel or Inline statement

NullInterpret

The defined symbol will when it occurs in a text file, Excel file or an inline statement be interpreted as NULL. A user-defined variable.

Example:

set NullInterpret=' ';

set NullInterpret =;

will NOT return null values for blank values in Excel (but it will for a csv text file)

set NullInterpret ='';

will return null values for blank values in Excel (but will NOT for a csv text files)

Example:

set NullInterpret ='';

EMP:

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

If(IsNull(SAL), 'NULL', 'NOT NULL') AS Status,

Alt(SAL,'NULL'&RecNo()) as SAL1 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

];

In the above script empty values are interpreted as null values.  If you remove that then it won't consider the empty values as null values.

Regards,

jagan.

6 Replies
MVP
MVP

Re: NULLINTERPRET

Arjunarao
Honored Contributor II

Re: NULLINTERPRET

Hi Manish,

I am unable to replicate. Could you please do it for me. Thanks in advance.

MVP
MVP

Re: NULLINTERPRET

Check the 2nd Link and go through the 1st Link's explanation on the function.

MVP
MVP

Re: NULLINTERPRET

You have to use in combination of below three.

SET NULLINTERPRET=;

SET NULLVALUE='Not Available';

NULLASVALUE *;

MVP & Luminary
MVP & Luminary

Re: NULLINTERPRET

Hi,

Check this from Qlikview help file, this applicable for only Text, Excel or Inline statement

NullInterpret

The defined symbol will when it occurs in a text file, Excel file or an inline statement be interpreted as NULL. A user-defined variable.

Example:

set NullInterpret=' ';

set NullInterpret =;

will NOT return null values for blank values in Excel (but it will for a csv text file)

set NullInterpret ='';

will return null values for blank values in Excel (but will NOT for a csv text files)

Example:

set NullInterpret ='';

EMP:

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

If(IsNull(SAL), 'NULL', 'NOT NULL') AS Status,

Alt(SAL,'NULL'&RecNo()) as SAL1 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

];

In the above script empty values are interpreted as null values.  If you remove that then it won't consider the empty values as null values.

Regards,

jagan.

Arjunarao
Honored Contributor II

Re: NULLINTERPRET

Thank you Manish and Jagan