Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Re: How to make intervals from years of birth?

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

10 Replies

Re: How to make intervals from years of birth?

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.

Not applicable

Re: How to make intervals from years of birth?

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

Re: How to make intervals from years of birth?

Hi,

     Can you able to say the start year of birth

Not applicable

Re: How to make intervals from years of birth?

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)

Re: How to make intervals from years of birth?

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

Not applicable

Re: How to make intervals from years of birth?

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;

Re: How to make intervals from years of birth?

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?

Not applicable

Re: How to make intervals from years of birth?

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

Re: How to make intervals from years of birth?

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