Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to make intervals from years of birth?

Hi,

I have data about year of birth, for example 89, 76, 01, etc. First I want to make them like years - 1989, 1976, 2001, and then to structute them in intervals ( to 20; 21-30; 31-40;41-50;51-60 and after 60), I think that the years are possible to be made in the script and the intervals in a function, so the function is the problem.

1 Solution

Accepted Solutions
MayilVahanan

Hi,

    

LOAD

    Left([Pa],2) as yearOfbirth;

T1:

     Load * from [FileName].[FileExtension]; // This is the file where Pa field is available

    

T2:

Load *, if(yearOfbirth <= 12, '20'&yearOfbirth,'19'&yearOfbirth) as AgeBorn Resident T1;

Load * ,

If(Year(Today()) - AgeBorn <= 20, '<=20',

If(Year(Today()) - AgeBorn >20 and Year(Today()) - AgeBorn <= 30, '21-30',

If(Year(Today()) - AgeBorn > 30 and Year(Today()) - AgeBorn <=40 , '31-40',

  If(Year(Today()) - AgeBorn >40 and Year(Today()) - AgeBorn <=50 , '41-50',

   If(Year(Today()) - AgeBorn >50 and Year(Today()) - AgeBorn <= 60 , '51-60', '>60'))))) as AgeDiff

Resident T2;

Futher you don't want T1 table means, you simply delete it, using

Drop table T1;

in this, '20'&yearOfbirth,'19'&yearOfbirth gives four number for year. so its gives

Year(Today()) - 1990 = 22 .

By your calculation,it gives Year(Today()) - 90 = 1922

so in age difference calculation gives difference.

Where [Pa] is year value??

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

10 Replies
MayilVahanan

Hi,

     Can you able to say about start year and end year of birth( 89, 76, 01).In this 2001 is end date of birth. but for your requirement which is end and start year of birth. From  that only, able to change the script and give suggestion to solve your problem.

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

The end year for all years of birth is the current year (2012).

MayilVahanan

Hi,

     Can you able to say the start year of birth

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

I have a table with different years, but only last 2 numbers- it's not 1989 , in the table it  is only 89...

I have to add 1900 in every field maybe, except the people who r born after 2000 year (00,01,02...12)

MayilVahanan

Hi,

Try this,

    

T1:

Load * Inline

[

yearOfbirth

89

76

01

12

];

T2:

Load if(yearOfbirth <= 12, '20'&yearOfbirth,'19'&yearOfbirth) as AgeBorn Resident T1;

Load * ,

If(Year(Today()) - AgeBorn <= 20, '<=20',

If(Year(Today()) - AgeBorn >20 and Year(Today()) - AgeBorn <= 30, '21-30',

If(Year(Today()) - AgeBorn > 30 and Year(Today()) - AgeBorn <=40 , '31-40',

  If(Year(Today()) - AgeBorn >40 and Year(Today()) - AgeBorn <=50 , '41-50',

   If(Year(Today()) - AgeBorn >50 and Year(Today()) - AgeBorn <= 60 , '51-60', '>60'))))) as AgeDiff

Resident T2;

Futher you don't want T1 table means, you simply delete it, using

Drop table T1;

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

I change it a little cause there are a lot of years, but still doesnt work. Something is wrong with T1.

LOAD

    Left([Pa],2) as yearOfbirth,

   T1:

Load * Inline

[

yearOfbirth

];

T2:

Load if(yearOfbirth <= 12, yearOfbirth) as AgeBorn Resident T1;

Load * ,

If(Year(Today()) - AgeBorn <= 20, '<=20',

If(Year(Today()) - AgeBorn >20 and Year(Today()) - AgeBorn <= 30, '21-30',

If(Year(Today()) - AgeBorn > 30 and Year(Today()) - AgeBorn <=40 , '31-40',

  If(Year(Today()) - AgeBorn >40 and Year(Today()) - AgeBorn <=50 , '41-50',

   If(Year(Today()) - AgeBorn >50 and Year(Today()) - AgeBorn <= 60 , '51-60', '>60'))))) as AgeDiff

Resident T2;

MayilVahanan

Hi,

    

T2:

Load if(yearOfbirth <= 12, yearOfbirth) as AgeBorn Resident T1;

In this, if yearOfbirth > 12,means what gives ? . Then is possible to check in next table. Can you please explain it . And What is in  [Pa]  field?

Did you try my previous post, that helps little bit?

Can you please post your sample data or qvw file?

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

Hi,

Thank you very much. It was really helpful.I  try it and it will work, only have to fix the the first part.

[Pa] is Personal Identification Number .Its  first two letters are the year of birth. So I take the first two of all numbers[  , ..data.png

MayilVahanan

Hi,

    

LOAD

    Left([Pa],2) as yearOfbirth;

T1:

     Load * from [FileName].[FileExtension]; // This is the file where Pa field is available

    

T2:

Load *, if(yearOfbirth <= 12, '20'&yearOfbirth,'19'&yearOfbirth) as AgeBorn Resident T1;

Load * ,

If(Year(Today()) - AgeBorn <= 20, '<=20',

If(Year(Today()) - AgeBorn >20 and Year(Today()) - AgeBorn <= 30, '21-30',

If(Year(Today()) - AgeBorn > 30 and Year(Today()) - AgeBorn <=40 , '31-40',

  If(Year(Today()) - AgeBorn >40 and Year(Today()) - AgeBorn <=50 , '41-50',

   If(Year(Today()) - AgeBorn >50 and Year(Today()) - AgeBorn <= 60 , '51-60', '>60'))))) as AgeDiff

Resident T2;

Futher you don't want T1 table means, you simply delete it, using

Drop table T1;

in this, '20'&yearOfbirth,'19'&yearOfbirth gives four number for year. so its gives

Year(Today()) - 1990 = 22 .

By your calculation,it gives Year(Today()) - 90 = 1922

so in age difference calculation gives difference.

Where [Pa] is year value??

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.