Replace Null Values

[JKoczeniak]

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

[vitaliichupryna]

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

[Vegar]

You can load the field takin the alt function.

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