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.
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.
date(date#(DateOfAssessment,'DDMMYYYY'),'DD/MM/YYYY') as new_DateOfAssessment
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.
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.
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:
(all the fields required)
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
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.