Nested If on mulitple conditions

[SomeDudeAtWork]

Hi all,

Can someone possibly help me identify what's wrong here.

Essentially I'm trying to group various things together, and create a seperate column with the new information, such as Alaska, Canada, United States , should be North America, then France, Germany, Italy, Spain etc to be Europe, and similarly the same for about 9 African countries. I have no means of changing the raw data, as it sits in another office.

I've been trying this

=if(Match([Name_of_Country],'Alaska','Canada','United States'),'North America')
=if(Match([Name_of_Country],'France','Germany','Ireland','Spain','Portugal','Romania'),'Europe')

and similarly for Africa and Asia later, was doing this to test but can't seem to spot what's going wrong.
 

[awaisqureshi · Accepted solution]

Hi @SomeDudeAtWork 

You should be able to do what you're trying using  applymap.

You can create a table with the correct mapping (countries and continent) and apply that when you load in your data.

Take a look at the example below.

MAP:
Mapping
LOAD * INLINE [
    Country, Continent
    Alaska, North America
    Canada, North America
    US, North America
    France, Europe
    Germany, Europe
];

Table:
load
*,
applymap('MAP',[Name_of_Country]) as Continent
Resident Table1;

[SomeDudeAtWork]

I'll give this a shot, thanks so much

[jonathandienst · Accepted solution]

This really requires adding a new column. A calculated dimension like this is often complex and hard to maintain. Is it possible to add a small table (from Excel, text or inline) that relates the regions and the countries?

Like this (inline for example):

LOAD * Inline
[
	Name_of_Country, Region
	North America, Canada
	North America, United States,
	Europe, France
	...etc...
];

Now just use the Region dimension.

It's not really necessary to join/applymap this as Qlik can handle the association.

[SomeDudeAtWork]

Had to include some minor changes on my side, as per the nature of work, but it worked thank you

[SomeDudeAtWork]

I was able to get it working thanks to your method, and a few changes on my end, as well as the other person who has contributed to this issue. Thank you very much