Mapping … and not the geographical kind

[Jennell_McIntire]

I often use some sort of mapping in the QlikView applications I create to manipulate the data.  Mapping functions and statements provide developers with a way to replace or modify field values when the script is run.  By simply adding a mapping table to the script, field values can be modified when the script is run using functions and statements such as the ApplyMap() function, the MapSubstring() function and the Map … using statement.

Let’s take a look at how easy it is to use mapping in a QlikView application.  Assume our raw data looks like this:

You can see the country United States of America was entered in various ways.  If I wanted to modify the country values so that US was used to indicate the United States of America, I could add a mapping table like this to map all the variations of the United States of America to be US.

Once I have a mapping table, I can start using it.  I usually use the ApplyMap() function when I am mapping.  The script below will map the Country field when this table is loaded.

The results are a table like the one below where all the Country values are consistent, even the one that was misspelled (Country field for ID 4).  The mapping handled all the variations that were entered in the data source and when the mapping value was not found the default ‘US’ was used.

Now I could have also used the Map … using statement to handle the mapping.  Personally, I have never used this statement but if you had many tables that loaded the Country field and you wanted to map each of them, Map … using provides an easier way of doing it with fewer changes to the script.  After loading the mapping table, you can say:

     ...

     load data

     ...

This will map the Country field using the CountryMap until it reached the Unmap statement or the end of the script.  The main difference between this and the ApplyMap() function is with the Map … using statement, the map is applied when the field is stored to the internal table versus when the field is encountered.

One last mapping function that is available in QlikView is the MapSubstring() function that allow you to map parts of a field.  Using the mapping table below, the numeric data in the Code field is replace with the text value.

Before MapSubstring() function is used:

After MapSubstring() function is used:

The numeric values in the Code field were replaced with the text values.

Mapping is a powerful feature of QlikView that I use in just about every application.  It allows me to “clean up” the data and format it in a consistent manner.  I often use it to help scramble data when I have many values that I need to replace with dummy data.  So the next time you are editing or “fixing” the data in your data source, consider mapping.  Check out the technical brief I wrote on this topic.

Thanks,

Jennell

[amit_shetty78]

Hi Steve,

Thanks for replying.

The inline statement is just to give a small example. The data is actually loaded from an excel document.Its a comma separated country list and without the quotes.

So do you suggest that I remove the comma from the country value and make it a continuous string?

Appreciate your help on this.

Thanks,

Amit.

[stevedark]

Hi Amit,

This probably should be spun off into a new thread, but anyway...

You don't need an INLINE or an ApplyMap and you can keep the commas as delimiters.  You just need to load twice from the spreadsheet, once with all fields except the country and then again with the unique ID and the Country field (with the SubField statement as above).

Steve

[#4]

Thanks Jennell,

You made it very easy to understand. One quick question, Is mapping load case specific? As i tried to map the value 'U' (in upper case) with US and did not throw the required result. When i mapped 'u' (in lower case) I got the right result.

Rahul

[#5]

Thanks for your email.

I ll be attending to it shortly

Regards

Femi

[stevedark]

Hi Rahul,

All matching of field values in QlikView is case sensitive.  Are you sure there is not an lower case u in the data? Generally sub string matches are not carried out by ApplyMap - unless you combine the statement with a Mid statement.

Steve

[#7]

Thanks for your email.

I ll be attending to it shortly

Regards

Femi

[#8]

Hi Steve, thanks for your reply. I got it, it has to be case sensitive.

The 'u' i was referring to was the Country in Table 1 from the above example.

Regards,

Rahul Agarwal

[siddheshmane]

Hi,

I'm new to qlikview. I have one question. What if I have a country UK in the same above example with other countries as US, USA, U.S etc. Will the UK value also be changed? or will it remain the same.( I know it's kinda lame question. )

Thanks.

[stevedark]

This depends on the third parameter of ApplyMap.  If only two parameters are given then the second parameter (that is being looked up and replaced) is given as the result of the function.  So, in the example above UK would remain unchanged.

You could add a third parameter, which can in turn be a function, for example if you wanted to flag it as missing but carry the value you could have:

ApplyMap('CountryMap', Country, 'Missing: ' & Country) as Country,

And this would replace UK with Missing: UK.

This is a good way of spotting missing values in a look-up table, whilst still keeping the underlying data.  The fact you can put pretty much what you like in that third parameter is very powerful, you could put a second ApplyMap, for example.  My blog post details many of the possibilities.

[siddheshmane]

stevedark‌ Thanks. That was really easy to understand.

Thanks a lot.