Create a Regional View

[gfisch13]

Hi - I have a Location field in my initial table that includes about 50 different countries.  I'd like to group those locations into 3 Regions - Americas, Asia, EMEA.   I tried using various expressions but I'm not getting anywhere close.  The more I read, it appears that I should do this in the script??  Can anyone offer a best practice or something easy to replicate???

Thanks, George

[swuehl]

Create a Mapping table in the script to map countries to regions in your data model:

MAP:

MAPPING LOAD * INLINE [

Country, Region

USA, Northern America

Canada, Northern America

France, Europe

.. // Etc. etc.

];

LOAD

     Country,

     ApplyMap('MAP',Country,'No region found!') as Region,

     ..

FROM ...;

Then use field Region in list boxes, charts, etc.

[#3]

Hi!

Several ways to do this...

Have you fried making a calculated dimension? Try adding a dimension by right clicking on your chart -> properties -> dimensions -> add calculated dimension.

You can add one by following this syntax:

if(Country='USA' or Country='Mexico', 'Americas',

if(Country='Japan' or Country='China','Asia',

EMEA))

You can also create an inline load to connect Country to Region, then add Region as a dimension. In the load statement add something like:

LOAD * INLINE [

    Country, Region

    USA, Americas

    Mexico, Americas

    China, Asia

    Japan, Asia

    France, EMEA

];

[ogster1974]

I would create a geo reference file in excel or similar containing the geographical hierarchy you need to support.  In your case 2 columns country and region and load in during the reload of your script.  Qlik will join on your country in your data model and you can use region straightaway in your charts

IF you do this  you can easily add new countries and in future extend your hierarchy to support sub regions, areas etc...

[gfisch13]

If I did this, where exactly would I add the text.  My script is attached.  Woiuld it be part of that table I'm loading or independent from it?  Thanks!!!

[gfisch13]

Thanks for the reposnse, I will try this...........One more question (this got deleted from my original post?.......

How would you suggest pulling values in a field, only those that say USD at the end???  Thanks!

[ogster1974]

‌do you mean adding default values in certain fields?  Your question isn't veryclear to me.   Do you have an example of what you are trying to do to share?

[swuehl]

George Fischetti wrote:

If I did this, where exactly would I add the text.  My script is attached.  Woiuld it be part of that table I'm loading or independent from it?  Thanks!!!

You need to define / put the MAP table before you access it using the ApplyMap() function. The ApplyMap() function would be called from within the LOAD that contains your Country field (Location?). If it's Location, insert this line in your LOAD statement you posted above (plus, as mentioned, the MAP: MAPPING LOAD ... ahead in the script):

LOAD

     Location,

    ApplyMap('MAP',Location,'No region found!') as Region,

     ..

If you have some locations that end with 'USD' and should all be mapped to e.g. 'America', you can also use something like

LOAD

     Location,

     If( Location Like '*USD', 'America','not America') as Region,

     ...

You can also combine both kind of location mapping

LOAD

     Location,

    If( Location Like '*USD', 'America', ApplyMap('MAP',Country,'No region found!')) as Region,

     ..

[gfisch13]

I'm sorry - I realize now how poorly worded my question was.   I'm talking about values in the field.  This table I'm using has fields that record money in both the local currency and also USD, so it would look like this:

Hungary LEI

Hungary USD

Japan YEN

Japan USD

I want to limit my records that I see to just the USD values and I'm not sure how to do it in QLikview.  I can do this using a Substring command in other software packages but unsure the best way to do it here!!   Thanks for your imput, I'm learning quite a bit!!

[ogster1974]

Use a WHERE clause in your data load script to only include records WHERE Currency='USD'; That should limit the data coming into your model to only those with USD Values.

Regards

Andy