Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

bhavvibudagam
Contributor II

How to convert - to null

Hi Experts,

Can anyone please help me on this.

I have the exported output file from qlikscript like below. Here I need to convert    ( - ) to Null.


I have tried like this in the script

if(IsNull(trim([AB Targe])),Null(),trim([AB Targe)) as AB Targe,


But getting the dash(-) in the result still now. Please help me how to convert to null.Please find the sample source file.

Output screenshot.png

Thanks in advance.

1 Solution

Accepted Solutions
devarasu07
Honored Contributor II

Re: How to convert - to null

Hi,

may be this?

Fact:

load *,if(len([AD Target])<=0 or [AD Target]='-' or isnull([AD Target]),'Null',[AD Target]) as [AD Target Revised],

if(len([CD Target])<=0 or [CD Target]='-' or isnull([CD Target]),'Null',[CD Target]) as [CD Target Revised];

LOAD * INLINE [

    ID, AD Target, CD Target

    1, 67, 2

    2, 0, 0

    3, -, -

    4, -, 2

    5, 55, -

];

Capture.JPG

View solution in original post

4 Replies

Re: How to convert - to null

What are you getting when you use this?

if(IsNull([AB Targe]),'Null',trim([AB Targe)) as AB Targe

OR

if(Len([AB Targe]) = 0,'Null',trim([AB Targe)) as AB Targe,

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
devarasu07
Honored Contributor II

Re: How to convert - to null

Hi,

may be this?

Fact:

load *,if(len([AD Target])<=0 or [AD Target]='-' or isnull([AD Target]),'Null',[AD Target]) as [AD Target Revised],

if(len([CD Target])<=0 or [CD Target]='-' or isnull([CD Target]),'Null',[CD Target]) as [CD Target Revised];

LOAD * INLINE [

    ID, AD Target, CD Target

    1, 67, 2

    2, 0, 0

    3, -, -

    4, -, 2

    5, 55, -

];

Capture.JPG

View solution in original post

devarasu07
Honored Contributor II

Re: How to convert - to null

Hi,

If your issue still not resolved, try to share the expected output and then check article as well,

NULL handling in QlikView

If u wish to handle "-" as "Null" in qlik front end then easily u can do it by enable check box in your pivot/table chart object--> presentation tabl  misisng symbol "-" into Null or NA like that

Capture.JPG

Re: How to convert - to null

There are many threads on Qlik Community with the help of this you can get the knowledge about how to handle null values in the Qlikview.

https://community.qlik.com/docs/DOC-3155

http://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/null-value-handling.htm

how to handle null values in qlikview