Derive birthday and gender from string

[hollowshrine]

Hi team 🙂 I'm working on my Master's thesis and I have a field that contains data for both the client's birthday and gender, formatted as such:

YYMMDD for male, e.g. 990101

YYMM+50DD for female, e.g. 995101

Does anyone know how to derive a column with just the date and a column with just the gender?

Any help would be immensely appreciated 🙂

[Taoufiq_Zarra]

can you share a sample data and the expected output ?

[Vegar]

Try script like below:

LOAD 
   *,
   Dayname(alt(date#(GenderDate,'YYMMDD'),date#(GenderDate-5000,'YYMMDD'))) as BDay,
   IF(mid(GenderDate,3,2)>12, 'Female', 'Male') as Gender 
Inline [
GenderDate
990101
995101
991231
996231
];

See attached qvw

[hollowshrine]

Sure 🙂 Attached in file.

[Vegar · Accepted solution]

LOAD
  *,
  Date(alt (date#(birth_number, 'YYMMDD'),date#(birth_number-5000, 'YYMMDD')), 'YYMMDD') as CalculatedBirthday,
  IF(mid(birth_number,3,2)>12, 'female', 'male') as CalculatedGender
;
LOAD
  birth_number,
  birthday as ExpectedBirthday,
  gender as ExpectedGender
FROM example.xls (biff, embedded labels, header is 1 lines, table is Sheet1$);