Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
gerhardl
Creator II
Creator II

Date format

Hi,

I'm importing a daily account extract into a qlikview document.

The date format on the account extract is DD-MMM-YY, where the MMM is text. So today's date will show as 08-Apr-11.

Qlikview doesn't recognize this as a date, only as text. What can I do to change this?

Thanks

1 Solution

Accepted Solutions
Not applicable

Sorry about the previous incomplete post... try this

Directory;
LOAD [Product Name],
[ID number],
[Account no],
[Card no],
DATE(DATE#( [Account open date], 'DD-MMM-YY')) as [Account Open Date],

and then rest of your fields...try it...

View solution in original post

7 Replies
Miguel_Angel_Baeyens

Hello Gerhard,

Try the following in your load script:

Date(Date#(DateField, 'DD-MMM-YY')) AS DateField


Hope that helps.

gerhardl
Creator II
Creator II
Author

Hi Miguel,

Sorry for being a Qlikview spaz - but could you be more specific?

Where exactly whould I put that in the script? SER DateFormat?

Also, when you say 'datefield' am I suppose to enter the names of the fields there? Because the extract has about 20 different date fields.

Miguel_Angel_Baeyens

Hello,

Your script (the piece of code you need to write to pull data from the database and put into memory) will look like the following

Table: // table labelLOAD TransID, // this are the fields you are loading into QlikView's memory CustID, Date(Date#(DateField, 'DD-MMM-YY')) AS DateField;SQL SELECT TransID, CustID, DateFieldFROM Table.Datasource;


The above is just an example just to see how to do the conversion in the load script, so change it to fit your needs.

Hope that makes more sense.

Not applicable


Miguel Angel Baeyens wrote:
Hello Gerhard,
Try the following in your load script:
<blockquote><pre>Date(Date#(DateField, 'DD-MMM-YY')) AS DateField

Hope that helps.

You need to do that in the load script and you and you will have to swap DateField for your date fields, and probably do it for each of you date fields

Regards

Jon

gerhardl
Creator II
Creator II
Author

My load script looks like this:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='R #,##0.00;R-#,##0.00';
SET TimeFormat='hh:mm:ss TT';
SET DateFormat='YYYY/MM/DD';
SET TimestampFormat='YYYY/MM/DD hh: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';

Directory;
LOAD [Product Name],
[ID number],
[Account no],
[Card no],
[Account open date],

and then the rest of my field names, and then

FROM
[Account Extracts\Daily_AccountExtract_A_07-APR-2011_08042011015858.txt]
(txt, codepage is 1252, embedded labels, delimiter is '~', no quotes, header is 1 lines);

So if I have a field named [Account Open Date], where exactly should I enter Date(Date#([Account Open Date], 'DD-MMM-YY')) AS [Account Open Date]

?

\Thanks

Not applicable

Hello,

Thats exactly what you need to do in your script. Give a try

ANDY

Not applicable

Sorry about the previous incomplete post... try this

Directory;
LOAD [Product Name],
[ID number],
[Account no],
[Card no],
DATE(DATE#( [Account open date], 'DD-MMM-YY')) as [Account Open Date],

and then rest of your fields...try it...