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

[aheavy95]

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 "-"

Thanks

Add tags

[BrunPierre]

I'd try something like this

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

[aheavy95]

Hi Peter it give me an error
any other idea?

[BrunPierre]

Works fine. What is the error?

[aheavy95]

---

@BrunPierre wrote:

Works fine. What is the error?

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

---

[BrunPierre]

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]

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

[Vegar]

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]

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