topic Re: Derive birthday and gender from string in QlikView

Derive birthday and gender from string

hollowshrine — Thu, 20 Aug 2020 21:11:29 GMT

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 🙂

Re: Derive birthday and gender from string

Taoufiq_Zarra — Thu, 20 Aug 2020 21:18:01 GMT

can you share a sample data and the expected output ?

Re: Derive birthday and gender from string

Vegar — Thu, 20 Aug 2020 21:41:28 GMT

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

Re: Derive birthday and gender from string

hollowshrine — Thu, 20 Aug 2020 21:43:24 GMT

Sure 🙂 Attached in file.

Re: Derive birthday and gender from string

Vegar — Thu, 20 Aug 2020 21:51:47 GMT

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$);