Using Mapping Table in Script

[triekong]

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!

[pedrobergo]

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]

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

[pedrobergo]

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

[rwunderlich · Accepted solution]

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]

Hi Rob,

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

[rwunderlich]

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]

Good to know ! thank you