Discussion Board for collaboration on QlikView Scripting.
I want to fill empty values with mapping by an Excel file. The table in Qlikview looks like this:
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!
Go to 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]
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().
You can load your mapping table with a mapping load and use ApplyMap:
Mapping LOAD H1,
LOAD [Header 1],
If(not IsNull([Header 2], [Header 2], ApplyMap('MappingTable', [Header 2])) as [Header 2]
I hope this will guide towards the right direction.
Is there any logic you have for which value you are applying for C and which value apply for D???
Yes, in this case I have an Excel file which looks like this:
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]
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]
If(IsNull([Header 2] OR LEN([Header 2]) = 0,
ApplyMap('YourMapTableName', [Header 2])
[Header 2] ) AS [Header 2]
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