topic Two different date formats in QlikView

Two different date formats

Anonymous — Tue, 23 Aug 2016 02:56:55 GMT

Can someone please help on this?

I came across this question in one of the project interview:

I have a column A with two different date formats 'MM/DD/YYYY' and 'DD/MM/YYYY' loaded and the final output is one format 'YYYY/DD/MM'. How to solve this without using alt function?

Can we use something like Date(Date#(Date#(A,'MM/DD/YYYY'),'DD/MM/YYYY'),'YYYY/DD/MM')?

Thanks

Re: Two different date formats

Anil_Babu_Samineni — Tue, 23 Aug 2016 03:19:59 GMT

Perhaps this

Date(Date#(A),'yyyy/dd/mm')

Re: Two different date formats

vishsaggi — Tue, 23 Aug 2016 03:28:42 GMT

May be this:

Date(Floor(ColumnA), 'YYYY/DD/MM')

Re: Two different date formats

Chanty4u — Tue, 23 Aug 2016 05:01:57 GMT

try below

alt( date#( A , 'MM/DD/YYYY' )
date#( A , 'DD/MM/YYYY' )
,'YYYY/DD/MM' )

Re: Two different date formats

jonathandienst — Tue, 23 Aug 2016 05:07:43 GMT

>>Can we use something like Date(Date#(Date#(A,'MM/DD/YYYY'),'DD/MM/YYYY'),'YYYY/DD/MM')?

No you can't. Alt is the correct way to solve this problem - Its a more complicated problem if you don't want to use Alt, so why would you want to avoid it?

=Date(Alt(Date#(A, 'MM/DD/YYYY'), Date#('DD/MM/YYYY')))

Change the order if you want DMY to take precedence over MDY for ambiguous dates.

Re: Two different date formats

MarcoWedel — Tue, 23 Aug 2016 06:02:01 GMT

maybe you can also use RangeMax or RangeMin instead of Alt.

hope this helps

regards

Marco

Re: Two different date formats

ramchalla — Tue, 23 Aug 2016 06:15:02 GMT

Hi Karthick,

I think this will help you.

T1:
LOAD Dates
FROM
Book2.xlsx
(ooxml, embedded labels, table is Sheet1);

LOAD
Date(Dates,'YYYY/DD/MM') as FormattedDates
Resident T1;