Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
SomeDudeAtWork
New 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
awaisqureshi
New Contributor III

Re: Nested If on mulitple conditions

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

MVP
MVP

Re: Nested If on mulitple conditions

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
awaisqureshi
New Contributor III

Re: Nested If on mulitple conditions

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

SomeDudeAtWork
New Contributor III

Re: Nested If on mulitple conditions

I'll give this a shot, thanks so much

MVP
MVP

Re: Nested If on mulitple conditions

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

SomeDudeAtWork
New Contributor III

Re: Nested If on mulitple conditions

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

Highlighted
SomeDudeAtWork
New Contributor III

Re: Nested If on mulitple conditions

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