Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator 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
Highlighted

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
Highlighted

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

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

Highlighted
Creator II
Creator II

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

Highlighted

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

Highlighted
Creator II
Creator II

There are more than 50 wild match values are there.

Highlighted
Partner
Partner

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.

Highlighted

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

Highlighted
Creator II
Creator II

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

Highlighted
Creator II
Creator II

Thanks, Steven.

Highlighted

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