Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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
Highlighted
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);

Highlighted
Creator II
Creator II

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.

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

Highlighted
Specialist III
Specialist III

How about

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

Highlighted
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

Highlighted
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

Highlighted
Contributor II
Contributor II

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

Highlighted
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

Highlighted
Contributor II
Contributor II

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