
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Convert 6 digit number to date
I tried to convert a 6 digit number like 010160 to a date in DDMMYYYY format to get 01-01-1960. The exact format of the column to convert to a new column is:
zoeknaam |
010160AC-1 |
180572BU-4 |
170258HO-5 |
190580EN-8 |
300551CA-11 |
190477DA-14 |
281069AD-15 |
So far I removed the last charactars exept the number by using the following code and I extracted the number 1 as a new column patiëntId:
NAV:
LOAD zoeknaam, geslacht, H_PC, geboortedatum, land, SubField(zoeknaam, '-', -1) as patiëntId,
Date(Date#(PurgeChar (SubField(zoeknaam, '-', -2), 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'),'DD-MM-YY'),'DD/MM/YYYY') as geboortedatumcheck
to convert to a date but it doesn't work. The output in geboortedatumcheck should be:
geboortedatumcheck |
01-01-1960 |
18-05-1972 |
17-02-1958 |
19-05-1980 |
30-05-1951 |
19-04-1977 |
28-10-1969 |
The code:
PurgeChar (SubField(zoeknaam, '-', -2), 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz') as geboortedatumcheck
works properly and gives the numeric values as:
geboortedatumcheck |
010160 |
180572 |
170258 |
190580 |
300551 |
190477 |
281069 |
How can I convert the 6 digit numbers to a date as shown?
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
To handle years after 1999, you would need to include a cut-off so years after 30 are processed as 19xx and years 30 or lower are processed as 20xx
Something like
Date(Date#(Left(zoeknaam,4) &
if( Mid(zoeknaam,5,2) > 30, '19' & Mid(zoeknaam,5,2), '20' & Mid(zoeknaam,5,2) ), 'DDMMYYYY')) as Date


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try it with:
date(date#(left(zoeknaam, 6), 'DDMMYY'), 'DD-MM-YYYY')
- Marcus
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How about this?
Date(Date#(Left(Field,6), 'DDMMYY'), 'DD-MM-YYYY') as geboortedatumcheck

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If your Year of date is fixed to be 1900+.. then try like:
Date(Date#(Left(zoeknaam,4)&'19'&Mid(zoeknaam,5,2), 'DDMMYYYY')) as Date
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Haha, Nice


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
To handle years after 1999, you would need to include a cut-off so years after 30 are processed as 19xx and years 30 or lower are processed as 20xx
Something like
Date(Date#(Left(zoeknaam,4) &
if( Mid(zoeknaam,5,2) > 30, '19' & Mid(zoeknaam,5,2), '20' & Mid(zoeknaam,5,2) ), 'DDMMYYYY')) as Date

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Colin,
...years after 30 are processed as 19xx and years 30 or lower are processed as 20xx
Didn't know that. But always wondered it would be something so. Is this documented somewhere? Could you please redirect?
Thanks


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This is a cut-off that you determine based on the value used for comparison in the if statement.
There is no standard for this, I just used 30 as an example.
If your data spans more than 100 years (which is possible if it is a date of birth) then there is no way to determine if the correct date is 19xx or 20xx from a 2-digit year!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I wonder if this is static or if it keeps on changing... because although it is still far away, we will eventually be in 2031 and at that time it would somehow need to read the date correctly

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
That's true. I just wanted to understand the logic Qlik follows. See, when I try to parse the data in YY format, qlik reads somewhere as 19XX and somewhere 20XX.
Date(Date#(Left(zoeknaam,6), 'DDMMYY')) as Date
It could be similar to what you explain, only the number could be different, may be 50 instead of your 30?

- « Previous Replies
-
- 1
- 2
- Next Replies »