topic Re: Convert .TXT file Date Format in QlikView

Convert .TXT file Date Format

billuran — Wed, 27 Jul 2016 14:24:39 GMT

Hello All, I have a text document that has a date format as YYYY-MM-DD.

I have a .csv file that has a date lookup relation as MM/DD/YYYY. How do I change the date format YYYY-MM-DD to be able to have the correct look up relationship as the .csv file. IE MM/DD/YYYY.

Thanks!

Re: Convert .TXT file Date Format

jpenuliar — Wed, 27 Jul 2016 15:55:16 GMT

Hi Bill,

Maybe something like below:

Date(MakeDate(subfield('2016-07-27','-',1),subfield('2016-07-27','-',2),subfield('2016-07-27','-',3)),'MM/DD/YYYY')

Jonathan

Re: Convert .TXT file Date Format

Colin-Albert — Wed, 27 Jul 2016 16:05:47 GMT

Try this

date(date#('2016-07-27', 'YYYY-MM-DD'), 'MM/DD/YYYY')

or date(date#(your_date_field, 'YYYY-MM-DD'), 'MM/DD/YYYY')

There are some useful posts on dates and date formatting here

Get the Dates Right

The Date Function

Why don’t my dates work?

Re: Convert .TXT file Date Format

Chanty4u — Thu, 28 Jul 2016 12:06:46 GMT

u can set here also

SET DateFormat='MM/DD/YYYY';

or else As colin

Try this

date(date#('2016-07-27', 'YYYY-MM-DD'), 'MM/DD/YYYY')

or date(date#(your_date_field, 'YYYY-MM-DD'), 'MM/DD/YYYY')