Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
aheavy95
Contributor III
Contributor III

if a certain field is null (-) insert "No value"

Hi all, 

In a straight table, for a specific column - Company 

I have to insert "No value" if the value is null.

I have tried 2 things, both didnt work:

1)  IF(IsNull([Company Name]),'No value',[Company Name] )

2)  if(len([Company Name])<1,'No value',[Company Name]) 

Feels like it worked for me in the past, no ideas whats wrong.

In both cases the expression was valid but the column still shows "-"

aheavy95_0-1660460649691.png

 



Thanks

Labels (3)
8 Replies
BrunPierre
Specialist
Specialist

I'd try something like this

IF(LEN(TRIM([COMPANY NAME]))= 0 ,'No value',[COMPANY NAME])

 

 

 

aheavy95
Contributor III
Contributor III
Author

Hi Peter it give me an error
any other idea?

BrunPierre
Specialist
Specialist

Works fine. What is the error?

peter_brown_1-1660465972795.png

 

 

 

aheavy95
Contributor III
Contributor III
Author


@BrunPierre wrote:

Works fine. What is the error?

peter_brown_1-1660465972795.png

 

 

Now the expression is valid but I still get the "-"

aheavy95_0-1660470791510.pngaheavy95_1-1660470795819.png

 


 

BrunPierre
Specialist
Specialist

Maybe be a combination of these, else show the contents of the Company column

IF(LEN(TRIM([COMPANY NAME]))= 0 OR [COMPANY NAME]=' ' OR [COMPANY NAME]='-'  ,'No value',[COMPANY NAME])

vinieme12
Champion III
Champion III

You can replace null values when loading data using NullAsValue

https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/Scripting/null-va...

 

 

example

NullAsValue fieldA;

Set NullValue = 'No Value';

temp:
Load if(len(fieldA),fieldA,Null()) as fieldA,fieldB inline [
fieldA,fieldB
,X
,Y
,Z
SomeValue,E
];

Capture.PNG

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Vegar
MVP
MVP

Maybe the Coalesce() function can be of help. (It is a pretty new function and will not be available if you are running an old Qlik Version). Try this:

=Coalesce( [Company Name],'No name')

ajaykakkar93
Specialist
Specialist

hi,

I agree @Vegar is correct, but if u want in across the app i have another way to do that 
for example, you have a table you want to remove (-) & change to some text or EMPTY can do that with THEME's

write the below code in theme.css if using Theme API

.qv-st-data-cell-null-value .qv-st-value .qv-st-value-overflow span:after {
content: "No value";
visibility: visible;
left: 0px;
position: absolute;
width: 60px;
}

.qv-st-data-cell-null-value .qv-st-value .qv-st-value-overflow span {
visibility: hidden;
position: relative;
}

or can use simple kpi & test the same above code for a table in qlik sense