NULLINTERPRET

[qlikviewwizard]

Hi All,

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

I enclosed the sample data.

[MK_QSL]

NULL handling in QlikView

Null Value Handling using 3 Value Logic

[qlikviewwizard]

Hi Manish,

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

[MK_QSL]

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

[MK_QSL]

You have to use in combination of below three.

SET NULLINTERPRET=;

SET NULLVALUE='Not Available';

NULLASVALUE *;

[jagan · Accepted solution]

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]

Thank you Manish and Jagan