Chart Dimensions

[EvanBarrick]

I am trying to create a simple pie chart. The dimension is 'Gender' and the Measure is =count(UserID).

The problem I am running into, is that the Gender field has the following values:

-FEMALE

-Female

-rbtFemale

-MALE

-Male

-rbtMale

I would like to group the values that contain Female into 'Female' and the values that have Male into 'Male'

How can I go about doing this?

[dplr-rn]

Why dont you add a new column to your dimension table
e.g. if(Index(lower(GENDER),'female')>0,'Female','Male') as Gender_New

or you can create a calculated dimension which does the same

[EvanBarrick]

I understand the logic but cannot make work for my solution.

Would there be a way in my script to do something along the lines of:

IF(Gender = 'Male' or Gender = 'MALE' or Gender = 'rbtMale') then maleGender

and then do the same for Female?

[dplr-rn]

Yes you can . But my solution is a little more efficient

Why doesn't the other one work? Share the one you used

Actually a better solution would be to load your substitution as a map.

E.g.

GenderMap:

Load  * inline [

Gender, GenderValue

Male, Male

rbtMale, Male

rbtMale, Female

]

Then use applymap on your load.

[EvanBarrick]

I am loading this as the map table:

GenderMap:
Mapping LOAD * INLINE
[Gender, GenderValue,
Male, Male
MALE, Male
rbtMale, Male
Female, Female
FEMALE, Female
rbtFemale, Female
];

Then in my main load, I am attempting to do this:

ApplyMap('GenderMap', Gender, 'GenderValue') as newGender,

however I get an error message of 'map_id' not found. Is my format incorrect?

[dplr-rn]

looks allright i checked locally with another dummy table and it works. check your script more carefully. 

to debug

1. Remove the "Mapping" prefix from the gendermap  load .

2. Place an "Exit Script;" statement after the load of the "GenderMap" load and then reload.

Does the "GenderMap" table appear in the table viewer?

Also i am assuming load with the applymap statement comes after the mapping load.

couple of other things.

remove the extra comma from load inline

Mapping LOAD * INLINE
[Gender, GenderValue,

should not be cause of error but your apply map does not need the 3rd parameter. third parameter is the default value

ApplyMap('GenderMap', Gender) as newGender

should be enough

[patilamay]

Create a new columns using this

if(WildMatch(Gender,'*Female*'),'Female','Male') as GenderNew

HTH