New Field from Existing Field values

[GreatGreekYogurt]

Hi Everyone!

I have a list of universities with number of students enrolled. Example of dataset provided below

LOAD * INLINE [

School,  Category,  Enrollment,  Total Enrollment

University of Phoenix-Arizona,  'Women',  134 722,  195 059

University of Phoenix-Arizona,  'Asian',       1 959,       195 059

University of Phoenix-Arizona, 'White',      58 209,      195 059

University of Phoenix-Arizona, 'Black',      31 455,      195 059

University of Central Florida,  'Women',     33 482,      60 767

University of Central Florida,  'Asian',          3 343,        60 767

University of Central Florida, 'White',         33 293,     60 767

University of Central Florida, 'Black',         6 400,        60 767

and so on and on...

My problem is that field Category is mixed with both gender ('Women') and Races, however there is no 'Men' in the list.

I would like to add values of the number of 'Men' enrolled (Total Enrollment - Women) for each university in the existing Category field.

After that I would like split the existing Category field into 2 new separate fields i.e. Gender field and Race field. All of this I want to do in Script Load Editor.

Could you give me some advice here, please?

Any tips appreciated, thank you!

[lironbaram]

hi 

so assuming the first table is called Table1 , you can add to your script 

concatenate (Table1)
load School,
     'Men' as Category,
      Total Enrollment - Enrollment   as Enrollment, 
      Total Enrollment
resident Table1
where Category = 'Women';

[GreatGreekYogurt]

Thank you, it worked. However this is only my first step of the issue.

Any pointers on how to take the 2 field values 'Men' and 'Women' from existing field Category and move them to a new field Gender? 

I have seen in similar problems someone using For loop, but I'm not familiar with that concept.
Also, I thought of creating 2 entirely new fields Men and Women and then joining them together in one field using Crosstable(), that might work.

[lironbaram · Accepted solution]

HI 

hi the ideal solution will be to be able to divide each gender to races

but if you can i would create to different tables ,

like this 

concatenate (Table1)
load School,
     'Men' as Category,
      Total Enrollment - Enrollment   as Enrollment, 
      Total Enrollment
resident Table1
where Category = 'Women';
///split table 1 to two tables /////////////////////////////////////
GenderTable:
load School,
     Category         as Gender,
     Enrollment       as genderEnrollment,
     Total Enrollment as genderTotalEnrollment
resident Table1
where match(Category,'Men','Women');

RaceTable:
load School,
     Category         as Race,
     Enrollment       as raceEnrollment,
     Total Enrollment as raceTotalEnrollment
resident Table1
where match(Category,'Men','Women')=0;

drop table Table1;