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

[swuehl]

Thanks, Jennell.

In your application, you are using a different applymap() statement than shown above in the image for the Country mapping code:

ApplyMap('CountryMap', Country, 'US') as Country,

This is using the default value you are mentioning in your text, though I wouldn't use a default value 'US', unless there are no possible typos in other possible countries contained in your mapping, I would rather use something like 'Mapping failed' or the original value to allow the developer / user to easily detect a misspelled / missing value in the mapping.

[Sokkorn]

Personally, I have never used this statement but if you had many tables that loaded the Country.....

Me too. never think about this kind of mapping script. Anyhow, thanks for share this tip.

Regards,

Sokkorn

[#4]

Thanks for the insight - I agree with swuehl, i would use Unknown or -1 for those which does not match, so i can easily point out the missing values.

[Anonymous]

What are the advantages of using "Map Using" instead of "ApplyMap".

What are the differences between them and what about performance.

Regards.

CB.

[#6]

Mapping load is also very useful for datacleaning.

Often you will want to map versus a part of a string while replacing the entire string with the mapped value.

this is easily done as follows:

M

Map_Table:

Mapping load

Match

,'|' & Tekst & '|' as Tekst

inline [

Match, Tekst

App, Appel

B, Banana

Cit, Citrus

Dad, Dadel

F, Fruit

];

Table:

Load

Nr

,[Order Description]

, rangeminstring('Other',subfield(mapsubstring('Map_Table',purgechar([Order Description], '|')),'|',2)) as 'Clean description'

inline [

Nr , Order Description

1 , Appel

2 , Inv.App.456

3 , Inv. Cit. 4756

4 , Inv. Dad 345

5 , Inv D432

6 , Fruit

7 , 34 Fruit 678 ordered

8 , Orange

];

which results in the following:

Nr	Order Description	Clean description
1	Appel	Appel
2	Inv.App.456	Appel
3	Inv. Cit. 4756	Citrus
4	Inv. Dad 345	Dadel
5	Inv D 432	Other
6	Fruit	Fruit
7	34 Fruit 678 ordered	Fruit
8	Orange	Other

What happens is the following:

Our mapping table has a field Tekst which is concatenated with a unique value |  on both sides, which does not appear in the data which we wish to clean.

So we will map the partial string App and replace it with |Appel|

The formula

rangeminstring('Other',subfield(mapsubstring('Map_Table',purgechar([Order Description], '|')),'|',2)) as 'Clean description'

first purges our unique value | from the data, in case it does appear somewhere.

Then we search our mapping table and replace all found strings.

For instance a result would be:  Inv.|Appel|.456

Subfield is used to take only the part between both occurances of our unique value |.

Some values will not have been mapped, for instance the 8th record in the table. This null value is replaced with Other by using rangeminstring.

p.s. this script part was not made by me, but a colleague.

[#7]

Thanks for your email.

I ll be attending to it shortly

Regards

Femi

[stevedark]

I'm a big fan of the ApplyMap statement, and it's good to see it being given some exposure here.

I created a blog post giving my reasons for why I think it is one of the neatest functions in the QlikView feature set, and examples of basic and advanced syntax for using it:

http://www.quickintelligence.co.uk/applymap-is-it-so-wrong/

Regards,

Steve

[amit_shetty78]

Hi All,

I have a scenario where I have a country column which can have a comma separate list of countries like:

Id     Country

1     'Italy, Denmark, Ireland, South Africa'.

This can be any number of countries:

I would like to have a list box of all countries where when I select say 'Denmark', the row with Id=1 should be highlighed, Same with any of the other 3 countries. For that I tried using applymap:

Country_Map:

Mapping load * INLINE [

Country                                                         CountryNew

'Italy, Denmark, Ireland, South Africa'          Italy

'Italy, Denmark, Ireland, South Africa'          Denmark

'Italy, Denmark, Ireland, South Africa'          Ireland

'Italy, Denmark, Ireland, South Africa'          South Africa

Load

     Id,

     ApplyMap('Country_Map', Country) As Country

FROM x;

This only maps the comma separate country list to the first country, in this case Italy.

An I doing something incorrect here? Is the right way to use ApplyMap? Is there a work around?

thanks.

[#10]

Thanks for your email.

I ll be attending to it shortly

Regards

Femi

[stevedark]

Hi Amit,

ApplyMap won't help you in that case, as it will only ever assign one value to a field.

The reason your mapping table is not giving the result you are expecting is because the comma in the Country name is being seen as the delimiter in the INLINE table - you can not override the comma using quotes here.

What you need to do is use the SubField statement.

Load your main table with all fields other than the Country.

Then do a separate load from the same field, with only the Id and a SubField statement on the Country:

Countries:

LOAD

   Id,

   SubField(Country, ',') as Country

FROM....

You will find the second table will have one row per country linking back to the main table.  This will then behave as you want it to.

Hope that helps.

Steve