Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Looks like Qlik has 1968 as the change between 19xx and 20xx
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.
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)
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
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.
Yes, that would make sense.. May be we could get a better explanation from hic .
+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.
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