Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
vidyasagar159
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
sunny_talwar

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
sunny_talwar

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
Creator II
Creator II
Author

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

sunny_talwar

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

vidyasagar159
Creator II
Creator II
Author

There are more than 50 wild match values are there.

stascher
Partner - Creator II
Partner - Creator II

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.

sunny_talwar

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

vidyasagar159
Creator II
Creator II
Author

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
Creator II
Creator II
Author

Thanks, Steven.

sunny_talwar

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