topic Re: How to replace null value only in one column in QlikView

How to replace null value only in one column

Tue, 15 Oct 2013 11:57:08 GMT

Hi,

I have two columns like below

Employee	Department
101	NULL
201	10
NULL	10
NULL	30

I have to replace Department NULL value = N/A but Employee NULL Value should remain same.

IN Straight table under presentation tab when I am using Null Symbol= N/A it is showing in Employee also which is not my case. I need N/A only in Department.

Could anyone help me how to do this please.

Thanks.

Re: How to replace null value only in one column

MayilVahanan — Tue, 15 Oct 2013 12:00:06 GMT

Try like this

Load

If(Len(Trim(Department)) = 0, 'N/A', Department) As Department

From TableName;

Then use Department in charts.

Hope that helps

Re: How to replace null value only in one column

tresB — Tue, 15 Oct 2013 12:00:51 GMT

Try expression like:

If(IsNull(Department), 'N/A', Department)

Re: How to replace null value only in one column

CELAMBARASAN — Tue, 15 Oct 2013 12:02:40 GMT

Its a global property it applies to all expression.

You should change the value in script or

in expression using if(IsNull(FieldName), 'NA', FieldName)

Re: How to replace null value only in one column

saumyashah90 — Tue, 15 Oct 2013 12:03:23 GMT

if Table1 is already loaded,

then

Table2:

NoConcatenate

Load

Employee,

If(Len(Trim(Department))=0,'N/A',Department) as Department
residentTable;

You can get it.

Re: How to replace null value only in one column

Tue, 15 Oct 2013 12:07:03 GMT

Thanks and if I want to replace Department 10='Sales' and Department NULL= 'Accounts'

will the below statement works?

If(Department= 10, 'Sales',IF(ISNULL(Department), 'Accounts')) as Dept

Re: How to replace null value only in one column

tresB — Tue, 15 Oct 2013 12:10:56 GMT

A little bit of tweak:

If(Department= 10, 'Sales',IF(ISNULL(Department), 'Accounts', Department)) as Dept

Include the Department in else part, otherwise rest departments would be ignored.

Re: How to replace null value only in one column

Tue, 15 Oct 2013 12:13:24 GMT

Thanks and it's working.

Re: How to replace null value only in one column

Tue, 15 Oct 2013 12:14:24 GMT

Thanks Saumya and it's helpful