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
vidyasagar159
Contributor II

Apply Map Function for IF conditions

Hello Every One,

I am using the following if condition in dimension expression.Since my data volume is more than 10Millions I am facing some performance issues.

=if(Match(CATEGORY,'Green Apples', 'Red Apples'),'Apples',

if(Match(CATEGORY,'Black Grapes','Green Grapes'),'Grapes',

if(Match(CATEGORY,'Green Mangos','Yelllow Mangos'),'Mangos','All other Fruits'

)))

Can you please suggest me how to increase the performance of using load script editor by using APPLYMAP() or any other alternative options?

Thanks,

-Vidya

1 Solution

Accepted Solutions

Re: Apply Map Function for IF conditions

Then it makes sense to maintain this in an Excel file....

MappingTable:

Mapping

LOAD CATEGORY,

     CATEGORY_NEW

FROM ....;

FactTable:

LOAD ...,

     ApplyMap('MappingTable', CATEGORY, 'All other Fruits') as CATEGORY_NEW

FROM....;

View solution in original post

10 Replies

Re: Apply Map Function for IF conditions

One way would be to use Pick(Match())... rather than if

Pick(WildMatch(CATEGORY, '*Apples*', '*Grapes*', '*Mangos*') + 1, 'All other Fruits', 'Apples', 'Grapes', 'Mangos')

vidyasagar159
Contributor II

Re: Apply Map Function for IF conditions

Thanks for looking into this Sunny.Does PICK MATCH build the expression even before the APP loads? My intention is to load all the data with the dimension expressions before the APP got loaded. So that the performance of the app can increase.

Thanks,

-Vidya

Re: Apply Map Function for IF conditions

Use this in the script... how many values do you have to map? Just 3 or are there a long list to maintain?

vidyasagar159
Contributor II

Re: Apply Map Function for IF conditions

There are more than 50 wild match values are there.

Partner
Partner

Re: Apply Map Function for IF conditions

you could use a map

FruitTypeMap:

mapping load * inline [

input,output

Green Apples,Apples

Red Apples,Apples

Black Grapes,Grapes

Green Grapes,Grapes

Green Mangos,Mangos

Yellow Mangos,Mangos

];

Load

applyMap('FruitTypeMap',CATEGORY,'All other Fruits') as FRUIT_TYPE

from .....

If the list is extensive, consider loading  it from  an external table or spreadsheet.

Re: Apply Map Function for IF conditions

Then it makes sense to maintain this in an Excel file....

MappingTable:

Mapping

LOAD CATEGORY,

     CATEGORY_NEW

FROM ....;

FactTable:

LOAD ...,

     ApplyMap('MappingTable', CATEGORY, 'All other Fruits') as CATEGORY_NEW

FROM....;

View solution in original post

vidyasagar159
Contributor II

Re: Apply Map Function for IF conditions

Hi Sunny,

Thanks for your input. One last thing I want to check. what if I have two categories?  How to arrange excel File?

Example:

if(match(category1,'carrots','onions'),'Vegetables',

if(match(category,'red apples','green apples'),'Apples',

'All other fruits'

))

Thanks,

-Vidya

vidyasagar159
Contributor II

Re: Apply Map Function for IF conditions

Thanks, Steven.

Re: Apply Map Function for IF conditions

You are looking to map from two different category fields? 1st preference given to category1? Something like this

MappingTable:

Mapping

LOAD CATEGORY,

     CATEGORY_NEW

FROM ....;

FactTable:

LOAD ...,

     ApplyMap('MappingTable', category1, ApplyMap('MappingTable', category, 'All other Fruits')) as CATEGORY_NEW

FROM....;