topic Date format in QlikView

Date format

gerhardl — Fri, 08 Apr 2011 14:06:31 GMT

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

Date format

Miguel_Angel_Baeyens — Fri, 08 Apr 2011 14:11:25 GMT

Hello Gerhard,

Try the following in your load script:

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

Hope that helps.

Date format

gerhardl — Fri, 08 Apr 2011 14:16:38 GMT

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.

Date format

Miguel_Angel_Baeyens — Fri, 08 Apr 2011 14:22:35 GMT

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.

Date format

Fri, 08 Apr 2011 14:22:42 GMT


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

Date format

gerhardl — Fri, 08 Apr 2011 14:45:29 GMT

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

Date format

Fri, 08 Apr 2011 16:29:38 GMT

Hello,

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

ANDY

Date format

Fri, 08 Apr 2011 16:31:58 GMT

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