This field is required.
Only these extensions are allowed(.jpg, .JPG, .jpeg, .JPEG, .gif, .GIF, .png, .PNG)
Tags cannot contain the characters ' /, \\, #, ?, or ; >,< '
Only these extensions are allowed(.zip,.ZIP,.pdf,.PDF,.qvf,.QVF,.qvw,.QVW)
Discussion Board for collaboration related to QlikView App Development.
I am trying to convert the date format from DD-MMM-YY to DD-MM-YYYY using the below expression.
Date type of the field from the source is Varchar. I am see issues with years <= 66. Example - YY 66 gets formatted to 2066 instead of 1966. However, >= 67 YY formats without any issues. Can someone please help me with this issue?
Issue is the source column being a varchar. IMHO it leads qlik to guess. Which leads to the results you mentioned
You might have to get down and dirty while loading the data
View solution in original post
on the script, you can try this:
if(Date(Date#(BIRTHDATE,'DD-MMM-YY'),'YY') <= 66, Date(Date#(BIRTHDATE,'DD-MMM-),'YY')&'-19'&Date(Date#(BIRTHDATE,'DD-MMM-YY'),'YY'), Date(Date#(BIRTHDATE,'DD-MMM-YY'),'DD-MM-YYYY'))
I kicked this over to our ETL team. They are tweaking it in the source. Thanks!
great. hope it works out
Thanks. I tried this. It works partially meaning, a few instances where BIRTHDATE is 01-APR-2011, YY = 11 which is <= 66 and adds a prefix 19 formats it as 1966 which is incorrect.
Ref taken from Colin albert's (Thanks @Albert for your expression) expr and tweaked. Check here: Convert 6 digit number to date
may be you can try like below: however you need to give a range for the year 19 to consider, I have taken the years between 30 and 67 will fall in 19 and anything apart will have 20. Hope this is ok?
= Date(Date#(Left('06-APR-66',7) &
if( Mid('06-APR-66',8,2) > 30 AND Mid('06-APR-66',8,2) < 67, '19' & Mid('06-APR-66',8,2), '20' & Mid('06-APR-66',8,2)), 'DD-MMM-YYYY'), 'DD-MM-YYYY')
my bad, didn't think about it.. if you add some conditions on it, it will work
Awesome! Tweaked your expression just a little bit and this solves my problem.
= Date(Date#(Left(BIRTHDATE,7) & if( Mid(BIRTHDATE,8,2) > RIGHT(YEAR(TODAY( )),2) AND Mid(BIRTHDATE,8,2) <= 99, '19' & Mid(BIRTHDATE,8,2), '20' & Mid(BIRTHDATE,8,2)), 'DD-MMM-YYYY'), 'DD-MM-YYYY')
vishsaggi - Thank you.
Glad it worked for you. However, i still suggest you to touch base with backend team if they can get the year in 4digits which makes life simpler.