Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
EvanBarrick
Creator
Creator

Chart Dimensions

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?

6 Replies
dplr-rn
Partner - Master III
Partner - Master III

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

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
Partner - Master III
Partner - Master III

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

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
Partner - Master III
Partner - Master III

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
Contributor III
Contributor III

Create a new columns using this

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

HTH