topic Re: Multiple Date format in excel issue in QlikView

Multiple Date format in excel issue

coolwaters — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Multiple Date format in excel issue

vishsaggi — Wed, 28 Jun 2017 14:45:49 GMT

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);

Re: Multiple Date format in excel issue

dmac1971 — Wed, 28 Jun 2017 14:53:10 GMT

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.

Re: Multiple Date format in excel issue

jmvilaplanap — Wed, 28 Jun 2017 14:57:19 GMT

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);

Re: Multiple Date format in excel issue

marcus_malinow — Wed, 28 Jun 2017 14:57:28 GMT

How about

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

Re: Multiple Date format in excel issue

antoniotiman — Wed, 28 Jun 2017 14:58:40 GMT

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

Re: Multiple Date format in excel issue

jmvilaplanap — Wed, 28 Jun 2017 14:59:32 GMT

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

Re: Multiple Date format in excel issue

coolwaters — Wed, 28 Jun 2017 15:01:58 GMT

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.

Re: Multiple Date format in excel issue

vishsaggi — Wed, 28 Jun 2017 15:17:39 GMT

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:

Re: Multiple Date format in excel issue

coolwaters — Wed, 28 Jun 2017 16:36:24 GMT

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);

Re: Multiple Date format in excel issue

vishsaggi — Wed, 28 Jun 2017 16:38:31 GMT

Can you attach your qvw file where you are getting those dashes for specific formatted dates. Are you still not getting the correct format. ?

Re: Multiple Date format in excel issue

antoniotiman — Wed, 28 Jun 2017 16:42:31 GMT

Re: Multiple Date format in excel issue

coolwaters — Wed, 28 Jun 2017 18:05:44 GMT

Thanks Antonio. Works

Thank you all for taking the time out to help.

Re: Multiple Date format in excel issue

coolwaters — Wed, 28 Jun 2017 18:11:05 GMT

Am still getting the -. Not sure why but the solution given by Antonio works so will move on.

Thanks again.

Re: Multiple Date format in excel issue

coolwaters — Wed, 28 Jun 2017 18:17:15 GMT

Would like to know how were you able to deal with two different date formats ie. DD-MM-YYYY hh:mm:ss ff & MM/DD/YYYY hh:mm:ss ff but just 'M/D/YYYY hh:mm:ss TT'. Pardon my ignorance here.

Re: Multiple Date format in excel issue

antoniotiman — Wed, 28 Jun 2017 18:18:43 GMT

What You see is cell representation of Excel according to Format Cell. However the content of cells for first format is like 42654.So is correct Date(DATE). Second format is what You see, then You need conversion.

Regards,

Antonio

Re: Multiple Date format in excel issue

antoniotiman — Wed, 28 Jun 2017 18:40:08 GMT

If I change Format Cell of First Formt Date, then

Re: Multiple Date format in excel issue

coolwaters — Thu, 29 Jun 2017 02:29:27 GMT

Thank you.