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: 
qlikviewwizard
Master II
Master II

NULLINTERPRET

Hi All,

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

I enclosed the sample data.

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

6 Replies
qlikviewwizard
Master II
Master II
Author

Hi Manish,

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

MK_QSL
MVP
MVP

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

MK_QSL
MVP
MVP

You have to use in combination of below three.

SET NULLINTERPRET=;

SET NULLVALUE='Not Available';

NULLASVALUE *;

jagan
Luminary Alumni
Luminary Alumni

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.

qlikviewwizard
Master II
Master II
Author

Thank you Manish and Jagan