Mapping Function Skipping Some Values

[MTS95]

Hi there,

I am trying to apply a mapping table to a field in Qlik. I would like to categorize a list of stores (called "agents" here) I was given by what type of store they are. For example, Abe's Eastover Hardware and Ace Hardware & Lumber Inc-c are both Hardware Stores.

Here is the top part of my mapping table, named [Agent_Type]:

Here is the script I use to apply that mapping table to the field AgentName and create the field AgentType (lines 10, 11, and 12):

However, when I create a table in a Qlik Sheet, not all of the stores are properly assigned to their categories. Notice that, in the table above, Ake Marine, Inc. (closed) should be listed as a Marine Supply Store, but below it is just given its own name again in the AgentType field:

I realize that reprinting the name in the AgentType field is probably the result of it being a null value or some other sort of else value (if I use ApplyMap('Agent_Type', AgentName, 'Wrong Category') as AgentType, then Ake Marine, Inc. (closed) gets the value Wrong Category in the AgentType field), but I can't figure out how to fix that.

There do not appear to be any extra characters in the mapping table (double spaces, apostrophes that shouldn't be there, things like that) that would make Qlik skip over those rows. What am I doing wrong?

Thanks!

[Mark_Little]

HI @MTS95 

Mapping on a text field can be tricky, I would start by using the TRIM() Function on the AgentName field on your ApplyMap. Also i remove your formatting on your inline load, i.e. remove the spaces and tabs, as these maybe seen ad field values.

[marcus_sommer · Accepted solution]

Beside the already mentioned spaces and tabs which may cause issues - whereby I think the inline-load applies already a trim() statement during the load - I assume that your mismatch is caused from the additionally commas within the agent name, like:

xyz, lcc                                                     ,  type xyz

because this comma is treated as field-delimiter.

You may bypass it by wrapping your values with double-quotes like "xyz, lcc" or maybe using another delimiter like | or # or ; which could be specified within the additionally file-format statement, like:

load * inline [
...
] (txt, delimiter is '|');

whereby as far as those values could be occur within your values you will run in an unnoticed mismatch-risk or you need to apply the above quotes-logic (the quotes itself could be also a challenge if they occur within the data).

Nowadays we use the inline-load very rarely but not for the above topics (they are not a problem else you need to be aware how the feature works and sometimes it requires additionally efforts) else to simplify the administration. A centralized Excel or data-base as source is much easier to overview and to handle and also to share as dozens of inline-load within dozens of applications.

- Marcus

[EdgarOlmos]

Hi
Checking the scenario to ensure that your data is doing the correct mapping, you can temporarily change your InLine Mapping to a simple InLine and with the Qlik association validate why it is being mapped that way to later adapt the InLine data to do the mapping that you are looking for.

Regards

[MTS95]

This is all excellent advice, and you were all correct: using the | as the delimiter instead of the comma and trimming the tabs and spaces I had out of the table solved the issue. Thank you!