Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Asuod_
Contributor III
Contributor III

Filling NULLs with Excel Mapping File

Hello, 

I have a variable 'Fund' in my Table1 and for any NULL values i would like it to match the document_number and document_date from Table1 with the Document_Number_Blank and Docuemnt_Date_Blank from mapping file and if it is a match then I would like to populate 'Fund' with the Fund_Blank from the MappingFile.  I wrote the script below but continue to get an error saying it cant find Document_Number_Blank. Appreciate any help!

MappingFile:

Load

Document_Number_Blank,

Document_Date_Blank,

Fund_Blank

.....;

 

Table1:

Document_Number,

Document_Date,

  If(Len(Trim(Fund))=0 or Isnull(Fund) and [Document_Number]=Document_Number_Blank and "Document Date" = Document_Date_Blank ,Fund_Blank, if(Len(Trim(Fund))=0 or Isnull(Fund),'Missing Fund', Fund)) as Fund

.....;

Labels (4)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

Maybe this will work for you.

 

MapFund:

MAPPING LOAD 

Document_Date_Blank &'_'& Document_Number_Blank,

Fund_Blank

FROM...;

 

Table1:

LOAD 

Document_Number,

Document_Date,

if(len(trim(Funds))>0, Funds, applymap ('MapFund', Document_Date &'_'& Document_Number,'Missing Fund') ...;

View solution in original post

2 Replies
Vegar
MVP
MVP

Maybe this will work for you.

 

MapFund:

MAPPING LOAD 

Document_Date_Blank &'_'& Document_Number_Blank,

Fund_Blank

FROM...;

 

Table1:

LOAD 

Document_Number,

Document_Date,

if(len(trim(Funds))>0, Funds, applymap ('MapFund', Document_Date &'_'& Document_Number,'Missing Fund') ...;

Asuod_
Contributor III
Contributor III
Author

Works perfectly, thank you!