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!
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]
Hi,
you can check by using the LEN() function whether the field [Header2] is filled or not and then act accordingly.
Check first whether that works as expected - it is not always that easy. IF LEN() doesn't work, try with ISTEXT().
HTH
You can load your mapping table with a mapping load and use ApplyMap:
MappingTable:
Mapping LOAD H1,
H2
FROM yourExcelFile;
FactTable:
LOAD [Header 1],
If(not IsNull([Header 2], [Header 2], ApplyMap('MappingTable', [Header 2])) as [Header 2]
FROM yourExcelFile;
I hope this will guide towards the right direction.
Best,
Sunny
Hi,
Is there any logic you have for which value you are applying for C and which value apply for D???
Regards
Yes, in this case I have an Excel file which looks like this:
Header 1 | Header 2 |
---|---|
A | Yes |
B | No |
C | Yes |
D | No |
E | Yes |
F | No |
And I want to substitute the empty field rows with this Excel file according to header 1.
Pass [Header 1] to Applymap
If(not IsNull([Header 2], [Header 2], ApplyMap('MappingTable', [Header 1])) as [Header 2]
Hi,
Thank you for your response, however it doesn't work. It does not recognize it as a correct code within the script.
In my case, I put it under map7.
If(not IsNull([Header 2], [Header 2], ApplyMap('Map7', [Header 2])) as [Header 2]
Hi,
Try this
DataTable:
LOAD [Header 1],
If(IsNull([Header 2] OR LEN([Header 2]) = 0,
ApplyMap('YourMapTableName', [Header 2])
[Header 2] ) AS [Header 2]
FROM YourSource;
Hi,
Thanks for the response. I understand your logic and it is a good alternative, however, I want a more direct solution.
Try as sunindia suggested above