Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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...
Hello Gerhard,
Try the following in your load script:
Date(Date#(DateField, 'DD-MMM-YY')) AS DateField
Hope that helps.
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.
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.
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
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
Hello,
Thats exactly what you need to do in your script. Give a try
ANDY
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...