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!

[datanibbler]

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

[sunny_talwar]

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

[PrashantSangle]

Hi,

Is there any logic you have for which value you are applying for C and which value apply for D???

Regards

[#5]

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.

[anbu1984]

Pass [Header 1] to Applymap

          If(not IsNull([Header 2], [Header 2], ApplyMap('MappingTable', [Header 1])) as [Header 2]

[#7]

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]

[Gabriel]

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;

[#9]

Hi,

Thanks for the response. I understand your logic and it is a good alternative, however, I want a more direct solution.

[anbu1984]

Try as sunindia suggested above