Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
JKoczeniak
Contributor
Contributor

Replace Null Values

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

2 Replies
vitaliichupryna
Creator III
Creator III

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

You can load the field takin the alt function.

ALT(HR_Active, 'Your alternative value') as HR_Active,
Plees ekskuse my Swenglish and or Norweglish spelling misstakes