Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
triekong
Contributor

Using Mapping Table in Script

I have a question on an issue we are facing with our geoMap chart.

i have a geoMap in my report, but in the dataset, some of the countries are listed with different names. For example, in the dataset, “China” is coming in as “Greater China” and this is not being able to be picked up by the GeoMap.

this is because the file i am using in the 'data' tab of my script (countries.csv  |  Dataset Publishing Language  |  Google Developers) , does not have locations listed as in my dataset.

so i have created an excel mapping table, in order to handle this and changed some of the countries's names based on my dataset's requirements.

My question is how do i use the excel mapping table in the load script so that when "Greater China" comes in from my dataset, it automatically gets mapped to "China" and "China" can now be used in geo chart on the report.

i have attached the report and mapping tables below.

Thanks!

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Using Mapping Table in Script

Rather than changing the TASK LOAD statement, I would suggest just adding this statement after the Mapping Load:

MAP [Assignee Geo Unit] USING MapNewGeoUnit;


-Rob

http://masterssummit.com

http://qlikviewcookbook.com

7 Replies
Partner
Partner

Re: Using Mapping Table in Script

Hi Ekong,

Your qvw have an Section Acess, so nobody can open it.

You must to comment the SA statement or put part of your code directly here.

Regards,

Pedro

triekong
Contributor

Re: Using Mapping Table in Script

thank you for that! i have edited out the SA. Let me know if you can view it now pedrobergo

Partner
Partner

Re: Using Mapping Table in Script

Hi Ekong

Yes, now I could open it, so follow my response.

First, to define a Mapping you must to put these:

MapNewGeoUnit:

Mapping Load * Inline

[OldAssign, NewAssign

Greater China,China

OldOtherAssign1, NewOhterAssign1

OldOtherAssign2, NewOhterAssign2

OldOtherAssign3, NewOhterAssign3

];

You can replace or add thes from-to lines as many you need, use enter to break registry lines.

Then you must change these line at the TASK load statement.

TASK:

LOAD DISTINCT

(...)

//     assigned_to_u_geographic_unit as [Assignee Geo Unit],

     ApplyMap('MapNewGeoUnit',assigned_to_u_geographic_unit,assigned_to_u_geographic_unit) as [Assignee Geo Unit],

(..)

Best Regards,

Pedro

MVP & Luminary
MVP & Luminary

Re: Using Mapping Table in Script

Rather than changing the TASK LOAD statement, I would suggest just adding this statement after the Mapping Load:

MAP [Assignee Geo Unit] USING MapNewGeoUnit;


-Rob

http://masterssummit.com

http://qlikviewcookbook.com

YoussefBelloum
Esteemed Contributor

Re: Using Mapping Table in Script

Hi Rob,

with your method, the Task table will get mapped automatically when you put MAP [Assignee Geo Unit] USING MapNewGeoUnit; before it ??

MVP & Luminary
MVP & Luminary

Re: Using Mapping Table in Script

Hi Youssef,

Anytime data is written to the field [Assignee Geo Unit] by a LOAD statement it will be mapped. So yes, the values in the Task table for this field will be mapped. This would also apply to any other table that created [Assignee Geo Map]. For simple mappings where you don't need to assign a default value, I find the MAP USING can be a little neater than ApplyMap(), but either works.

-Rob

YoussefBelloum
Esteemed Contributor

Re: Using Mapping Table in Script

Good to know ! thank you