Merging two fields with values

[Anonymous]

Hi All,

I have two columns in my Table Diverse. it looks like following:

Gender(US) and Gender(UK). 

Gender(US) has value 0,blank and Female.  Gender(UK) have values 0, blank, X, WBE and Female.

What I want to do is make a single field GENDER having values of Gender(UK) as it has even the values of Gender(US) ,but on selection it should give me both the records of Gender(UK) and Gender(US).

Any help would be appreciated.

Thanks.

[MarcoWedel]

can you post an example of the results of some selections.

thanks

regards

Marco

[Anonymous]

Hi marco,

That won't be possible but i think i explained the complete thing above.

Gender(US) have values:

blank

0

Female

Gender(UK) have values:

blank

0

Female

WBE

X

I want a final field with values:

blank

0

female

WBE

X

and on selection i should be able to get the records for both Gender(US) and Gender(UK).

Thanks.

[adamdavi3s]

Why not split it into two

Gender, Country

Then you can simply use set analysis or filters to work out your expressions, something along the lines of:

LOAD 'UK' as country, Gender(UK) as Gender

RESIDENT fact

WHERE Gender(UK) >0;

CONCATENATE

LOAD 'US' as country, Gender(US) as Gender

RESIDENT fact

WHERE Gender(US) >0;

And then something like

count({<Gender={'x'}>}Gender) as gender_x

count({<Gender={'x'},country={'UK'}>}Gender) as gender_x_uk

etc

Please remember to mark this answer as helpful or correct if it is.

[Anonymous]

Hey Adam,

This is complicating the things. Moreover it is just not this one field, there are in total 23 fields like this which i need to merge.

Any other suggestions would be really helpful.

Thanks

[Anonymous · Accepted solution]

Hi,

Try a crosstable.. see attached..

Hopefully this helps you!

[Aurelien_Martinez]

Hi,

you can use crosstable

CrossTable(Country, gender)
LOAD * INLINE [
  id, genderUS, genderUK
  1, F, W
  2, 0, 1
];

Aurélien

Consultant Excelcio

[MarcoWedel]

I guess the already suggested crosstable solution should serve you best, particularly with many source fields.

regards

Marco