topic Re: Different date format in one field in QlikView

Different date format in one field

gaurav2017 — Tue, 27 Nov 2012 10:08:38 GMT

I want to know that how to deal with this type of data attached below in the CSV format. If we have different type of date format in the data source, then how to make it look in a single format?

I know below code is not right, but help me people to correct this one.

This is my code:

Load Date,

Data,

date#(date, 'DD-MMM-YYYY'),

date#(num(date, 'DD-MMM-YYYY'))

from .\data\Test01.csv;

Regards,

Re: Different date format in one field

Anonymous — Tue, 27 Nov 2012 10:13:57 GMT

Hi Gaurav,

What format does the Date field in your CSV file have at the moment? Or actually what formats, since you mention multiple formats. Can you give an example of each?

Also, how would you like the values to be formatted in QlikView?

Re: Different date format in one field

gaurav2017 — Tue, 27 Nov 2012 10:27:13 GMT

There are mixed values

Numerical format: 01/01/2012

Text format: 01-Jan-2012, 01-01-2012, 01/Jan/2012

in the field "date"

& I want it all of them in a single common format, either it would be in text or inumerical.

Regards,

Re: Different date format in one field

hic — Tue, 27 Nov 2012 10:39:43 GMT

Alt( Timestamp#(MixDate,'M/D/YYYY h:mm tt'),

Date#(MixDate,'M/D/YYYY'),

Date#(MixDate,'D/M/YYYY'),

Date#(MixDate,'YYYYMMDD'),

Date#(MixDate,'DD.MM.YYYY'),

Date#(MixDate,'YYYY-MM-DD')

) as MixDate

HIC

http://community.qlik.com/docs/DOC-3102