topic Re: How to convert - to null in QlikView

How to convert - to null

bhavvibudagam — Wed, 25 Nov 2020 16:16:04 GMT

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.

Thanks in advance.

Re: How to convert - to null

Anil_Babu_Samineni — Tue, 05 Dec 2017 08:46:38 GMT

What are you getting when you use this?

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

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

Re: How to convert - to null

devarasu07 — Tue, 05 Dec 2017 10:09:42 GMT

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, -
];

Re: How to convert - to null

devarasu07 — Tue, 05 Dec 2017 10:51:05 GMT

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

Re: How to convert - to null

its_anandrjs — Tue, 05 Dec 2017 15:26:50 GMT

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