topic Re: Show date from date field with different date formats in QlikView

Show date from date field with different date formats

Sat, 15 Sep 2012 14:24:00 GMT

Hi there i get every day a csv file that contains transaction logs from different databases. Because of this the date field has many different types of date formats in it. I include the date field in the attached excel file.

I want to display all of the different date formats that are include in the field, in the following format: e.g. 11/11/2012. ( 'DD/MM/YYYY').

Unfortunately the code i wrote applies to certain date formats, not to all of them.

Any help would be very appreciated.

Re: Show date from date field with different date formats

hic — Sat, 15 Sep 2012 15:04:15 GMT

Timestamp(

Alt(

Timestamp#([Timestamp],'DD/MM/YYYY hh:mm[:ss]'),

Timestamp#([Timestamp],'DD-MM-YYYY hh:mm[:ss]')

)

,'DD/MM/YYYY hh:mm') as Timestamp

or if you want it as a date:

Date(

floor(

Alt(

Timestamp#([Timestamp],'DD/MM/YYYY hh:mm[:ss]'),

Timestamp#([Timestamp],'DD-MM-YYYY hh:mm[:ss]')

)

,'DD/MM/YYYY') as Date

In both cases, you can have a list of different date formats inside the Alt() function. QlikVIew will try the different formats, one after another.

HIC

Re: Show date from date field with different date formats

whiteline — Sat, 15 Sep 2012 15:05:19 GMT

This expression seems to load all correct as numeric timestamps:

alt(timestamp#([Last parameters download date/time], 'D/M/YYYY h:m'), timestamp#([Last parameters download date/time], 'D/M/YYYY h:m:s'))

Then you can use date() function to change the format as you want:

date(alt(...), 'DD/MM/YYYY')

But if you want not just display date in that format but also discard time information, you should use floor() function before:

date(floor(alt(...)), 'DD/MM/YYYY')

Re: Show date from date field with different date formats

Sat, 15 Sep 2012 15:45:33 GMT

Thank you both for your quick responses!

You solved this problem for me!

Thank you again guys!!!

Re: Show date from date field with different date formats

Fri, 03 Oct 2014 20:34:47 GMT

SUper thanks!