Multiple Date format in excel issue

[coolwaters]

Hi

I have a date field in excel with two different formats have tried using Timestamp # () Date # () also ALT () but am not getting it right. Please advise.

Note: Using QlikView 11.20 Personal Edition thus any solution please post the script will not be able to access other QVW.

Thanks.

[vishsaggi]

Try this?

LOAD *,

     Date(DateNum, 'MM/DD/YYYY hh:mm:ss tt') AS DateFormat1;

LOAD DATE,

     Num(Floor(DATE)) AS DateNum

FROM

Date.xlsx

(ooxml, embedded labels, table is Sheet1);

[dmac1971]

Hi give this a try :

LOAD if(Wildmatch(DATE,'*AM*'),Date(PurgeChar(DATE,'AM'),'MM/DD/YYYY'),Date(DATE)) As DATE

Seems to work ok for me.  There are probably far more elegant solutions out there though!!

Dermot.

[jmvilaplanap]

Hi

This works

LOAD

  Alt(Date#(DATE, 'DD/MM/YYYY hh:mm'), Date#(DATE, 'DD/MM/YYYY hh:mm:ss tt')) AS DATE

FROM

[Date.xlsx]

(ooxml, embedded labels, table is Sheet1);

[marcus_malinow]

How about

Date(Alt(Timestamp#(Date,'DD/MM/YYYY  hh:mm:ss'), Timestamp#(Date,'MM/DD/YYYY hh:mm:ss')))

[antoniotiman · Accepted solution]

May be

LOAD Alt(Date(DATE),Date(Date#(DATE,'M/D/YYYY hh:mm:ss TT'))) as DATE
FROM
"https://community.qlik.com/servlet/JiveServlet/download/1296152-284950/Date.xlsx"
(ooxml, embedded labels, table is Sheet1);

Regards,

Antonio

[jmvilaplanap]

Hi Dermot

You can't purge anything if you need to know the time as well. If you don't use the time, is better to convert the date to number because it uses less memory.

Regards

[coolwaters]

Hi Vishwarath,

Thanks for the reply but this only converts one set of dates and the other does not get considered.

So the file has the below to set of dates.

1) DD-MM-YYYY hh:mm:ss ff - Which is in date format and gets converted.

2) MM/DD/YYYY hh:mm:ss ff - Which is a string and gets ignored.

[vishsaggi]

Can you show me your load script. For me i loaded your excel sheet and do not find any - in those fields. For me i see this:

[coolwaters]

The Script is below:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='DD-MM-YY';

SET TimestampFormat='DD-MM-YY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

DATA:

LOAD *,

     Date(DateNum, 'MM/DD/YYYY hh:mm:ss tt') AS DateFormat1;

LOAD DATE,

     Num(Floor(DATE)) AS DateNum

FROM

Date.xlsx

(ooxml, embedded labels, table is Sheet1);