topic Re: Replace Null Values in QlikView

Replace Null Values

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

I have the below script loading data from two excel sheets. the "AD" table does not have the "HR_Active" field so once the two tables are joined there are a lot of Null values. I would like to replace all the Null Values in the HR_Active field with a value.

AD:

LOAD DisplayName, AccountExpirationDate, mail, EmployeeNumber, Description, Enabled,
extensionAttribute11 as WorkDayID, SamAccountName
FROM
[\\NACL60\users_kl\koczeja\QlikView Windows 10 Reports\Canada AD Review\CA01_AD_Export_3_14.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

HR:

LOAD [Employee ID] as WorkDayID, [Legacy Employee ID], Worker, [Legal Name], [Worker's Manager], [Job Title],
[Email - Work], HR_Active
FROM
[\\NACL60\users_kl\koczeja\QlikView Windows 10 Reports\Canada AD Review\Employee_List_-_HR (56).xlsx]
(ooxml, embedded labels, table is [Employee List - HR]);

Re: Replace Null Values

vitaliichupryna — Fri, 15 Mar 2019 15:52:24 GMT

Hi,
There are two possible solution:
1) If statement
if(isnull(HR_Active), 'Your Value', HR_Active) AS HR_Active
2) You can use NULLASVALUE function
Add variable at the start of the script
Set NullValue = 'Your Value';
Write list of fields were nulls should be replaced
NULLASVALUE HR_Active

This way allows load data without if statement

Thanks,
Vitalii

Re: Replace Null Values

Vegar — Fri, 15 Mar 2019 18:20:15 GMT

You can load the field takin the alt function.

ALT(HR_Active, 'Your alternative value') as HR_Active,