Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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....;
One way would be to use Pick(Match())... rather than if
Pick(WildMatch(CATEGORY, '*Apples*', '*Grapes*', '*Mangos*') + 1, 'All other Fruits', 'Apples', 'Grapes', 'Mangos')
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
Use this in the script... how many values do you have to map? Just 3 or are there a long list to maintain?
There are more than 50 wild match values are there.
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.
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....;
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
Thanks, Steven.
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....;