Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
sunindia 's solution is what you have to do
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]
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
Hi,
I get the following error:
Error in expression:
IsNull takes 1 parameter
missed closed
If(IsNull([Header 2] ) OR LEN([Header 2]) = 0,
but see my post on the null also
Thank you, your answer worked! Much obliged
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