Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have looked a many forum solutions, but I have been unable to resolve my problem so I have bitten the bullet and posted a question!
I have three fields in my SQL database:
YEAROFASSESS - char(4)
MONTHOFASSESS - char(2)
DAYOFASSESS - char(2)
When I load these my script reads:
....
DAYOFASSESS + MONTHOFASSESS + YEAROFASSESS as DateOfAssessment, etc
I want to use DateOfAssessment as the date field for the Calendar so I have tried converting it to a date:
date(DateOfAssessment) as ControlDate,
OR
date(date#(DateOfAssessment('DD/MM/YYY')) as ControlDate
(I even tried changing the definition of the DateOfAssessment to DAYOFASSESS + / + MONTHOFASSESS + / + YEAROFASSESS...)
Unfortunately none of this worked and I get 'date (or date#) is not a recognised function name' error message.
To try and understand what was happening I tried importing dates in text format(26082011) from an excel file and attempting to using the date() function to convert the values. It did not work , and I got an error message stating that I had the wrong number of arguments for the function.
This at least told me the date() function is valid, but maybe not for SQL via an OLEDB connection?
Can someone please explain what I am doing wrong and come up with a possible solution to convert character fields into a date format?
Many thanks in advance
Jason
Jason,
Take a look at the following example. All the fields I'm using both in the SQL and in the LOAD are guesses, but you'll get the point
Table: // Name of the table. Not required but very usefull and a must if you use STORE
LOAD ID, // All these fields come from the SQL
Company,
Name,
AddressTo,
MakeDate(Year, Month, DayNo) AS Date, // I'm creating a new field in QlikView based on 3 coming from SQL
Sales,
Discount,
Sales * (1 - (Discount / 100)) AS NetValue, // Another new field to QlikView based on existing
If(Sales * (1 - (Discount / 100)) > 100, 'Expensive', 'Cheap') AS Type;
SQL SELECT ID, Company, Name, AddressTo, Year, Month, DayNo, Sales, Discount
FROM Database.Table;
Hope that helps.
BI Consultant
Jason,
At a first glance, this line is getting trouble
DAYOFASSESS + MONTHOFASSESS + YEAROFASSESS as DateOfAssessment
Concatenation of literal strings in QlikView is done with the "&" instead
DAYOFASSESS & MONTHOFASSESS & YEAROFASSESS as DateOfAssessment
And if those values are numbers (although they are typed as char in the database, you can use the following
MakeDate(YEAROFASSESS, MONTHOFASSESS, DAYOFASSESS) AS Date
And this should work, returning a Date in QlikView format.
Hope that helps.
BI Consultant
Hi,
Consider that the date in text format coming from your datasource is in DDMMYYYY format from field name "DateOfAssessment".
For example 12082011,13082011,14082011,15082011,16082011,.......
So to convert this into date you need to do this.
Load,
date(date#(
DateOfAssessment,'DDMMYYYY'),'DD/MM/YYYY') as new_DateOfAssessmentFrom xyz.
Let me explain what does this expression mean.
here the date#() takes the two parameter, first is the text and second is the format in which you have date in your datasource (i.e 'DDMMYYYY' ), Then that is passed as one of the parameter of date function and the other parameter (i.e 'DD/MM/YYYY') is the format in which you want to conver.
Hope you understood.
Regards,
Kaushik Solanki
Hi,
What Miguelsaid is correct.
This is special with Excel as in Excel dates are not stored exactly as we see them in cells.
Say you have date like 20110826, then use makedate ( left ( date, 4 ), mid ( date, 5, 2 ), right ( date, 2 ) );
HTH
Miguel, Kaushik,
Thanks for the amazingly quick response.
Unfortunately, both solutions resulted in the same error message I was getting before; namely:
ErrorMsg: 'date#' (or MakeDate) is not a recognized function name'
Is this because I am importing from an SQL database?
Regards
Jason
Jason,
I'm assuming you are doing a LOAD preceding the SQL SELECT statement:
Table:
LOAD
MakeDate(YEAROFASSESS, MONTHOFASSESS, DAYOFASSESS) AS Date;
SQL SELECT YEAROFASSESS, MONTHOFASSESS, DAYOFASSESS
FROM Database.Table;
There are two different parts and the LOAD is what allows you to clean your data up, create new fields, format and so.
Neither MakeDate nor Date# are SQL functions, and that's why you are getting this error.
Hope that makes sense.
BI Consultant
Miguel,
No I do not have a LOAD statement, so your point about it not being an SQL statement makes sense. Whilst I understand your point I am struggling to think I how I compose my script as the day, month and year of assess are not the only fields I select from that table?
At the moment the table on the script as follows:
SQL Select
(all the fields required)
FROM table
With the statement below I do not see where I can select all the other fields.
LOAD MakeDate(YEAROFASSESS, MONTHOFASSESS,DAYOFASSESS) AS
SQL SELECT YEAROFASSESS, MONTHOFASSESS, DAYOFASSESS
SELECT
FROM table
Regards
Jason
Jason,
Take a look at the following example. All the fields I'm using both in the SQL and in the LOAD are guesses, but you'll get the point
Table: // Name of the table. Not required but very usefull and a must if you use STORE
LOAD ID, // All these fields come from the SQL
Company,
Name,
AddressTo,
MakeDate(Year, Month, DayNo) AS Date, // I'm creating a new field in QlikView based on 3 coming from SQL
Sales,
Discount,
Sales * (1 - (Discount / 100)) AS NetValue, // Another new field to QlikView based on existing
If(Sales * (1 - (Discount / 100)) > 100, 'Expensive', 'Cheap') AS Type;
SQL SELECT ID, Company, Name, AddressTo, Year, Month, DayNo, Sales, Discount
FROM Database.Table;
Hope that helps.
BI Consultant
Hi,
If you want other fields too then you can specify in load statment only like this.
LOAD
MakeDate(YEAROFASSESS, MONTHOFASSESS,DAYOFASSESS) AS DATE,
YEAROFASSESS,
MONTHOFASSESS,
DAYOFASSESS;
SQL SELECT YEAROFASSESS, MONTHOFASSESS, DAYOFASSESS
FROM table;
Regards,
Kaushik Solanki
Miguel,
That worked perfectly. Thanks very much for your time and help and thank you to everyone else who took the time to read my question and assist.
Out of curiosity I will now look at the excel load and see if I understand that one out too!
Regards
Jason