Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Nested If on mulitple conditions

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.
 

Labels (3)
2 Solutions

Accepted Solutions
Highlighted
Contributor III
Contributor III

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;

 

View solution in original post

Highlighted

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

5 Replies
Highlighted
Contributor III
Contributor III

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;

 

View solution in original post

Highlighted
Contributor III
Contributor III

I'll give this a shot, thanks so much

Highlighted

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

Highlighted
Contributor III
Contributor III

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

Highlighted
Contributor III
Contributor III

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