Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
jim_chan
Contributor III

how to get correct AGE from identity card number

Hi guys, 

Please help me out. 

1)  i m trying to use this script to get the correct year 19xx, instead the wrong year 20xx

=date(date#(left('630502101234',6),'YYMMDD')) 

The result is 2/5/2063. my expected result should be 2/5/1963

2) i m trying to use back the same script to get the AGE, but i got a negative age, is -26

=Age(Today(),date#(left('630502101234',6),'YYMMDD'))

 

Thanks.

Jim

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: how to get correct AGE from identity card number

Try this code:

 

If(left('030502101234',2) > (Year(Today(1)) - 2000)
,MakeDate(1900 + left('030502101234',2),Mid('030502101234',3,2),Mid('030502101234',5,2))
,MakeDate(2000 + left('030502101234',2),Mid('030502101234',3,2),Mid('030502101234',5,2))
)

 

 

play around with different values

View solution in original post

9 Replies
Partner
Partner

Re: how to get correct AGE from identity card number

This is not fool proof, but my take is:
If Left('630502101234',2) > 19 then use 1900s else 2000s.
e.g .
value = 100502101234
2 possible values = 1910 or 2010
if formula returns 1910, person is about 109 years old (not that it is impossible).
If formula returns  2010, person is about age 9

Highlighted
jim_chan
Contributor III

Re: how to get correct AGE from identity card number

Thanks. But do you have a workable formula?

Highlighted
Partner
Partner

Re: how to get correct AGE from identity card number

Try this code:

 

If(left('030502101234',2) > (Year(Today(1)) - 2000)
,MakeDate(1900 + left('030502101234',2),Mid('030502101234',3,2),Mid('030502101234',5,2))
,MakeDate(2000 + left('030502101234',2),Mid('030502101234',3,2),Mid('030502101234',5,2))
)

 

 

play around with different values

View solution in original post

Highlighted
jim_chan
Contributor III

Re: how to get correct AGE from identity card number

Hi, 

This part- If(left('030502101234',2), need to add and date() or date#, else it will only return value '03' rite?

Jim

 

Highlighted
simrankaur
New Contributor III

Re: how to get correct AGE from identity card number

=date(date#('19' & '' & left('630502101234',6),'YYYYMMDD'))

Highlighted
jim_chan
Contributor III

Re: how to get correct AGE from identity card number

63 is just 1 if the data I have , how bout is 00, or 11?

Highlighted
Partner
Partner

Re: how to get correct AGE from identity card number

if you try playing around with the formula like below:

left('030502101234',2)
03

Date(left('030502101234',2))
03/01/1900

Date#(left('030502101234',2))
03

having the results in number form makes it easier to troubleshoot and perform maths

2019 - 2000 = 19 

2000 + 03 = 2003 

Highlighted
jim_chan
Contributor III

Re: how to get correct AGE from identity card number

Thanks. Let me try it out. Will come back to you. 

Highlighted
jim_chan
Contributor III

Re: how to get correct AGE from identity card number

Hi, 

i have tested it, the logic works for me! thank you very much for coming out with this solution.

Thanks!

Rgds, 

 

Jim Chan