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: 
jim_chan
Specialist
Specialist

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
jpenuliar
Partner - Specialist III
Partner - Specialist III

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
jpenuliar
Partner - Specialist III
Partner - Specialist III

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

jim_chan
Specialist
Specialist
Author

Thanks. But do you have a workable formula?

jpenuliar
Partner - Specialist III
Partner - Specialist III

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

jim_chan
Specialist
Specialist
Author

Hi, 

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

Jim

 

simrankaur
Contributor III
Contributor III

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

jim_chan
Specialist
Specialist
Author

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

jpenuliar
Partner - Specialist III
Partner - Specialist III

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 

jim_chan
Specialist
Specialist
Author

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

jim_chan
Specialist
Specialist
Author

Hi, 

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

Thanks!

Rgds, 

 

Jim Chan