Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
coolwaters
Contributor II
Contributor II

Multiple Date format in excel issue

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.

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

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

View solution in original post

17 Replies
vishsaggi
Champion III
Champion III

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
Creator III
Creator III

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
Specialist
Specialist

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
Partner - Specialist III
Partner - Specialist III

How about

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

antoniotiman
Master III
Master III

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
Specialist
Specialist

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
Contributor II
Contributor II
Author

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.

DATE.png

vishsaggi
Champion III
Champion III

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:

Capture.PNG

coolwaters
Contributor II
Contributor II
Author

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