Loading a character field as a date from an SQL database

[#1]

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

[Miguel_Angel_Baeyens]

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.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[kaushiknsolanki]

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_DateOfAssessment

    From 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

[#4]

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

[#5]

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

[Miguel_Angel_Baeyens]

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.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#7]

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

[Miguel_Angel_Baeyens · Accepted solution]

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.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[kaushiknsolanki]

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

[#10]

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