topic Re: Create a Regional View in QlikView

Create a Regional View

gfisch13 — Mon, 09 Nov 2015 18:57:50 GMT

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

Re: Create a Regional View

swuehl — Mon, 09 Nov 2015 19:44:20 GMT

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.

Re: Create a Regional View

Mon, 09 Nov 2015 19:47:48 GMT

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

];

Re: Create a Regional View

ogster1974 — Mon, 09 Nov 2015 19:49:37 GMT

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...

Re: Create a Regional View

gfisch13 — Mon, 09 Nov 2015 19:58:17 GMT

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!!!

Re: Create a Regional View

gfisch13 — Mon, 09 Nov 2015 20:00:29 GMT

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!

Re: Create a Regional View

ogster1974 — Mon, 09 Nov 2015 21:03:01 GMT

‌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?

Re: Create a Regional View

swuehl — Mon, 09 Nov 2015 21:13:17 GMT

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,

Re: Create a Regional View

gfisch13 — Tue, 10 Nov 2015 14:04:24 GMT

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!!

Re: Create a Regional View

ogster1974 — Tue, 10 Nov 2015 14:40:20 GMT

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

Re: Create a Regional View

swuehl — Tue, 10 Nov 2015 15:17:51 GMT

As I tried to explain earlier, you can use LIKE operator (or WildMatch() function) to create a condition for record filtering:

LOAD

MoneyRecord

FROM ...

WHERE MoneyRecord LIKE '*USD';

...

WHERE WildMatch(MoneyRecord, '*USD');

...

WHERE RIGHT(MoneyRecord, 3) = 'USD';

Or maybe also

...

WHERE SUBFIELD(MoneyRecord,' ',-1) = 'USD';

Re: Create a Regional View

gfisch13 — Tue, 10 Nov 2015 18:26:00 GMT

Thank you to both of you for your help. I'm new to Qlikview so just trying to understand where everything goes. This was a big help and learning experience.