topic Re: Convert 6 digit number to date in QlikView

Convert 6 digit number to date

qlikmark1990 — Tue, 09 Jan 2018 12:01:29 GMT

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?

Re: Convert 6 digit number to date

marcus_sommer — Tue, 09 Jan 2018 12:05:18 GMT

Try it with:

date(date#(left(zoeknaam, 6), 'DDMMYY'), 'DD-MM-YYYY')

- Marcus

Re: Convert 6 digit number to date

Anil_Babu_Samineni — Tue, 09 Jan 2018 12:05:57 GMT

How about this?

Date(Date#(Left(Field,6), 'DDMMYY'), 'DD-MM-YYYY') as geboortedatumcheck

Re: Convert 6 digit number to date

tresB — Tue, 09 Jan 2018 12:21:25 GMT

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

Re: Convert 6 digit number to date

Anil_Babu_Samineni — Tue, 09 Jan 2018 12:24:19 GMT

Haha, Nice

Re: Convert 6 digit number to date

Colin-Albert — Tue, 09 Jan 2018 12:43:26 GMT

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

Re: Convert 6 digit number to date

tresB — Tue, 09 Jan 2018 13:35:19 GMT

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

Re: Convert 6 digit number to date

Colin-Albert — Tue, 09 Jan 2018 13:38:49 GMT

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!

Re: Convert 6 digit number to date

sunny_talwar — Tue, 09 Jan 2018 13:38:52 GMT

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

Re: Convert 6 digit number to date

tresB — Tue, 09 Jan 2018 13:44:54 GMT

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?

Re: Convert 6 digit number to date

Colin-Albert — Tue, 09 Jan 2018 13:57:34 GMT

Looks like Qlik has 1968 as the change between 19xx and 20xx

Re: Convert 6 digit number to date

tresB — Tue, 09 Jan 2018 14:04:18 GMT

Thanks Colin!

Yes, it seems that Qlik interprets <=67 in 19XX format and >=68 in 20XX format when it comes to parse a date in YY format.

Re: Convert 6 digit number to date

sunny_talwar — Tue, 09 Jan 2018 14:04:37 GMT

Could this be 50 years from today's year? I mean I wonder what we will see if we run the same app on 01/09/2019 (note the year)

Re: Convert 6 digit number to date

sunny_talwar — Tue, 09 Jan 2018 14:05:38 GMT

Again, do you think it is static? I somehow feel that it should not be static.... but I might be wrong... as I have never done this testing... I hope I can remember to come back to this thread 1 year from now

Re: Convert 6 digit number to date

Colin-Albert — Tue, 09 Jan 2018 14:08:31 GMT

I wonder if the cut off is 50 years either side of the current year?

we are in 2018 now, so 67 or lower is converted as 20xx, whereas 68 or higher is 19xx.

Doesn't leave much hope for those of us over 50 when it comes to converting 2-digit dates!

I can't see anything in the help files on this cut-off yet.

Re: Convert 6 digit number to date

tresB — Tue, 09 Jan 2018 14:10:03 GMT

Yes, that would make sense.. May be we could get a better explanation from hic‌‌ .

Re: Convert 6 digit number to date

Colin-Albert — Tue, 09 Jan 2018 14:11:57 GMT

+50/-50 is a simple answer but a bit odd as not m,any apps look 50 years into the future, but 50+ years back is common when dealing with ages.

Re: Convert 6 digit number to date

hic — Wed, 10 Jan 2018 12:11:19 GMT

A two-digit year is in its nature ambiguous, so if you want to be in control, you need to do something like what Colin suggests. Or avoid two-digit years in your source data...

The logic is indeed +50/-50, counted from the year the document was created.

HIC