Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 🙂
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$);
can you share a sample data and the expected output ?
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
Sure 🙂 Attached in file.
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$);