Fill empty value with mapping

[#1]

Hi,

I want to fill empty values with mapping by an Excel file. The table in Qlikview looks like this:

Header 1	Header 2
A	YES
B	NO
C
D

I have an Excel file with which I can substitute the empty values with YES or NO according to header 1 (in this case according to C and D). How can I do this?

Thank you in advance!

[PrashantSangle]

Hi,

Then You can link your QVD datafile with excel file on the basis of Header1,

Try like

File1:

Load Header1,

Header2

from QVDFile;

Left Join

Load Header1,

Header2 as Header_Temp

from ExcelFile;

Final:

Load Header1,

If(isnull(Header2),Header_Temp,Header2) as finalHeader2

Resident File1;

Drop table File1;

Regards

[#3]

sunindia 's solution is what you have to do

[anbu1984 · Accepted solution]

You should use [Header 1] in ApplyMap(). If [Header 2] is not null, then you can try this

If(Len(Trim([Header 2])) > 0, [Header 2], ApplyMap('Map7', [Header 1])) as [Header 2]

[#5]

This will work,

though I would maybe suggest just using the len, as that will catch the true nulls and empty string, so no need for the isnull also

YourMapTableName:

Mapping Load

[Header 1]

,[Header 2]

From excel..

DataTable:

LOAD [Header 1],

           If(LEN([Header 2]) = 0,

               ApplyMap('YourMapTableName', [Header 2])                                                 

                                         [Header 2]  )                                  AS [Header 2]

FROM YourSource;

Joe

[#6]

Hi,

I get the following error:

Error in expression:

IsNull takes 1 parameter

[#7]

missed closed

          If(IsNull([Header 2] ) OR LEN([Header 2]) = 0,

but see my post on the null also

[#8]

Thank you, your answer worked! Much obliged

[mohammadkhatimi]

Use [Header 1] in ApplyMap(). If [Header 2] is not null, then you can try this

If(Len(Trim([Header 2])) > 0, [Header 2], ApplyMap('Map7', [Header 1])) as [Header 2]

Regards,

Mohammad