Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
Employee
Employee

Mapping … and not the geographical kind

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:

Data.png

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.

CountryMap.png

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.

ApplyMap.png

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.

ApplyMap results.png

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:

map using.png

     ...

     load data

     ...

unmap.png

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:

Before.png

CodeMap.png

Mapsubstring.png

After MapSubstring() function is used:

After Mapsubstring.png

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

23 Comments
MVP
MVP

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.

0 Likes
287 Views
Sokkorn
Honored Contributor

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

0 Likes
287 Views
Not applicable

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.

0 Likes
287 Views
carbal1952
Contributor II

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

What are the differences between them and what about performance.

Regards.

CB.

0 Likes
287 Views
Not applicable

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:

NrOrder DescriptionClean description
1AppelAppel
2Inv.App.456Appel
3Inv. Cit. 4756Citrus
4Inv. Dad 345Dadel
5Inv D 432Other
6FruitFruit
734 Fruit 678 orderedFruit
8OrangeOther

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.

287 Views
Not applicable

Thanks for your email.

I ll be attending to it shortly

Regards

Femi

0 Likes
287 Views
MVP & Luminary
MVP & Luminary

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

287 Views
amit_shetty78
Contributor II

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.

0 Likes
287 Views
Not applicable

Thanks for your email.

I ll be attending to it shortly

Regards

Femi

0 Likes
287 Views
MVP & Luminary
MVP & Luminary

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

0 Likes
287 Views
amit_shetty78
Contributor II

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.

0 Likes
287 Views
MVP & Luminary
MVP & Luminary

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

0 Likes
287 Views
Not applicable

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

0 Likes
287 Views
Not applicable

Thanks for your email.

I ll be attending to it shortly

Regards

Femi

0 Likes
287 Views
MVP & Luminary
MVP & Luminary

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

0 Likes
287 Views
Not applicable

Thanks for your email.

I ll be attending to it shortly

Regards

Femi

0 Likes
287 Views
Not applicable

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


0 Likes
287 Views
siddheshmane
Contributor

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.

0 Likes
287 Views
MVP & Luminary
MVP & Luminary

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.

0 Likes
287 Views
siddheshmane
Contributor

stevedark‌ Thanks. That was really easy to understand.

Thanks a lot.

287 Views
Not applicable

very use full, and good explanation

thank you very much

0 Likes
287 Views
Not applicable

Thanks for your email.

I ll be attending to it shortly

Regards

Femi

0 Likes
287 Views
Chanty4u
Esteemed Contributor III

Excellent

0 Likes
287 Views