SQL Date Issues

[#1]

I have the below script and I am trying to get day, month, year and quarter. I constatnly get errors no matter which example I use from the forums, use inline or preload formatting ... I tried the preload both before and after the odbc

Here is one section - you can also see all latest commented out sections that failed. There was a lot more but I went through and deleted them

LOAD "First Reported On"

//     Num(Month("First reported On")) AS NumMonth, 

     Month("First Reported On") AS Month, 

     ;

ODBC CONNECT32 TO [AR System ODBC Data Source] (XUserId is KccBcRNMBDZaWQZNEA, XPassword is OfMBXYQGIKYEHWVMZbWB);

PBI:

SQL   SELECT

  PBI.Assignee,

    PBI."Assigned Group",

    PBI."Assignee Pblm Mgr",

//    PBI."Category",

  PBI."Assigned Group Pblm Mgr" as "PM Group",

    PBI."Description" as "Short Description",

    PBI."Detailed Decription" as "Description",

    PBI."First Name",

    PBI."First Reported On" as "First Reported On",

//    MONTH(PBI."First Reported On") as "Month",

    PBI.Impact,

    PBI."Invesitgation Status Reason",

    PBI."Investigation Driver",

    PBI."Investigation Status" as Status,

//    PBI."Known Error Created",

    PBI."Known Error Created Date",

    PBI."Last Modified By",

    PBI."Last Modified Date",

//    month(Timestamp#(PBI.'Last Modified Date', 'MM/DD/YYYY hh:mm:ss')) as Month,

    PBI."Last Name",

//    PBI."Organization",

  PBI."PBM_CI" as "Problem CI",

    PBI."Prev Status-PBI" as "Previous Status",

    PBI.Priority,

    PBI."Problem Investigation ID" as "Ticket Number",

    PBI."Product Name",

//    PBI.ServiceCI,

//    PBI."Status History",

    PBI."Status History.Assigned.TIME" as "Assigned Time",

//    PBI."Status History.Cancelled.TIME",

//    PBI."Status History.Cancelled.USER",

//    PBI."Status History.Closed.TIME",

//    PBI."Status History.Closed.USER",

    PBI."Status History.Completed.TIME" as "Completed Time",

    PBI."Status History.Completed.USER" as "Completed User",

    PBI."Status History.Under Investigation.TIME" as "Investigation Time",

    PBI."Status History.Under Investigation.USER" as "Investigation User",

    PBI."Status History.Under Review.TIME" as "Under Review Time",

    PBI."Status History.Under Review.USER" as "Under Review User",

    PBI."Submit Date",

    PBI."Submitter",

    PBI."Support Group Name Requester",

    PBI."Target Resolution Date",

    PBI."Temporary Workaround" as "Workaround",

    PBI.Urgency,

    PBI."Status History" as "Status History"

//    month(Timestamp#("First Reported On", 'MM/DD/YYYY hh:mm:ss')) as month

FROM "PBM:Problem Investigation" PBI

WHERE

PBI."Investigation Status" not in ('Cancelled','Closed')

and "Assigned Group Pblm Mgr" in ('Problem Managers','USB PM Pblm Mgr - DEFAULT')

;

[Gysbert_Wassenaar]

The sql select statement is sent to the database and is interpreted by the database. Most databases don't know Qlikview functions like timestamp#. You'll get an error from the database if it doesn't understand the sql statement you passed to it. You can either use the native functions that the database does know about or you can use a preceding load and use the Qlikview specific functions in the preceding load:

MyTable:

Load a, b, c, month(Timestamp#("First Reported On", 'MM/DD/YYYY hh:mm:ss')) as month;

SQL select a,b,c, "First Reported On" from ...etc;

[alex_millan]

Hi Chuck

limiting your script to the datefield, could you try this out:

Load

     Date,

     Day(Date) As Day,

     Num(Month(Date)) As Monthnumber,

     Year(Date) As Year,

     Ceil(Num(Month(Date))/3) As Quarter;

Load

     Date(Floor("First reported on"),'MM/DD/YYYY') As Date       //or whatever format you may need

;

SQL Select

PBI."First reported on"

from...

Hope that it would help,

Cheers,

[#4]

I got this error

Error in expression:

')' expected

Load

     Date,

     Day(Date) As Day,

     Num(Month(Date) As Monthnumber,

     Year(Date) As Year,

     Ceil(Num(Month(Date))/3) As Quarter

[#5]

I found the ) issue and fixed it now I have this

Field not found - <First reported on>

PBI:

[#6]

What do the a,b,c refer too??

[alex_millan]

Edit my previous answer correcting the missing parenthesis and the field in the sql sentence.

Seems that the field is not found when executing the sql select, could this be?

I'm not a big fan of those type of field names, including " ", perhaps this can be a part of the issue?

Check the correct name of the field, and put the name also in the load sentence right above the sql sentence.

Let's see if it works

[Gysbert_Wassenaar]

Just examples because I'm to lazy to copy and paste all your field names.

[#9]

I was able to get this to work but now the Day, Month, Quarter etc is not linked to any of the tables. How do I get "Dates" to line up with PBI and PKE

[alex_millan]

Wow, I think that's enough for another thread 😉

IMHO you should think again about your data estructure, PBI and PKE share a lot of fields and that is causing a synthetinc key to be created.

To link your date fields with your fact tables, the best solution is to make a master calendar table. Seek for further help in this fórum.

At least, you had a hint to get those date fields!

Cheers!