4 Replies Latest reply on Sep 25, 2016 11:07 PM by Hima vardhan Reddy Pavuluri

# finding unknown gender from secondary source

Well, I have a quite few empty fields for gender. I compiled a gender database from a different sources based on first names (I know it is not a 100% accurate method still, but it is better option compared to having empty fields), where I would like to use gender if first in original source is equal to first name in secondary source. Unknown is not a NULL, it is 'Unknown'. FYI.  Here is what I mean

My original source

 ID Name Gender 1 Alex Male 2 Liza Female 3 Alison Female 4 Eric Male 5 Adam Unknown 6 Bruce Unknown 7 June Unknown

Secondary source:

 Name Gender Adam Male Bruce Male June Female

I have tried calculated field, but no luck so far. Any idea how to handle this? Thank a lot.

• ###### 1. Re: finding unknown gender from secondary source

Hi Sherzodbek,

Please find attached the workbook for solution. Let me know if doesn't help!!!

• ###### 2. Re: finding unknown gender from secondary source

Hi ,

This is what you are expecting ?

I have created 2 Data sources as you just mentioned and created a calculation like this.

IF ATTR([Gender]='Unknown') THEN ATTR([Secondary].[Gender])

ELSE ATTR([Gender])

END

so where ever you are having Unknown for gender in primary data source it will take gender from secondary data source. You can use this calculation for Gender.

Hope this is what you are expecting.

• ###### 3. Re: finding unknown gender from secondary source

Yes, I was also able to create calculated field as below:

if (ATTR([Gender]))='Unknown' and ATTR([Firstname])= ATTR([Gender By Name].[First Name]) THEN ATTR([Gender By Name].[Gender])

ELSE ATTR([Gender]) END

But, now I have another problem, where I would like to use this calculated field as dimension, but I can't convert it to measure at all as it is aggregated. If I use as filter, it doesn't give me any list (Male, Female) but All and *.

Any further idea? Thank you again

• ###### 4. Re: finding unknown gender from secondary source

Hi ,

with same calculation i am able to see the values Male,Female,All when i use it as Filter. Please find the below screenshot.