topic Re: SQL Date Issues in QlikView

SQL Date Issues

Wed, 26 Mar 2014 16:32:27 GMT

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')

;

Re: SQL Date Issues

Gysbert_Wassenaar — Wed, 26 Mar 2014 16:40:42 GMT

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;

Re: SQL Date Issues

alex_millan — Wed, 26 Mar 2014 16:44:35 GMT

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,

Re: SQL Date Issues

Wed, 26 Mar 2014 16:53:22 GMT

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

Re: SQL Date Issues

Wed, 26 Mar 2014 16:54:32 GMT

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

Field not found - <First reported on>

PBI:

Re: SQL Date Issues

Wed, 26 Mar 2014 16:54:51 GMT

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

Re: SQL Date Issues

alex_millan — Wed, 26 Mar 2014 17:04:59 GMT

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

Re: SQL Date Issues

Gysbert_Wassenaar — Wed, 26 Mar 2014 17:11:54 GMT

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

Re: Re: SQL Date Issues

Wed, 26 Mar 2014 17:45:17 GMT

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

Re: SQL Date Issues

alex_millan — Thu, 27 Mar 2014 08:23:09 GMT

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!

Re: Re: SQL Date Issues

Gysbert_Wassenaar — Thu, 27 Mar 2014 08:45:14 GMT

1. Concatenate PKE and PBI into one table. That will get rid of the massive synthetic key.

PBIandPKE:

sql select ...pki_fieldnames_here..., 'PBI' as Source from ...pbisource_here...;

sql select ...pke_fieldnames_here..., 'PKE' as Source from ...pkesource_here...;

2. Your Dates table needs a field that also exists with the same name in the other table. Qlikview associates tables using the fields that have the same case-sensitive names in the tables.

Re: SQL Date Issues

PrashantSangle — Thu, 27 Mar 2014 08:54:52 GMT

Hi,

Looking at your data structure your PBI and PKE contain same fields,

So that you can concatenate both table and for understanding you can create flag

by adding

'PBI' as flag in PBI table and

'PKE' as flag in PKE table

Then use date field from your newly created table rename that field as Date so that it will create linking between new table and Dates table

Regards,

Re: SQL Date Issues

Thu, 27 Mar 2014 18:41:59 GMT

I have no idea how this structure would look 😕

I am telecom network guy not a programmer or DBA

Re: SQL Date Issues

Thu, 27 Mar 2014 18:45:03 GMT

Here is the whole thing... isnt the $syn table created because of the "as" statements? That was the only way I could find to get them to link together.. The bold Italics below is what I cant get to sync with anything else

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h: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';

load dual(String,Num) as Month inline

[String,Num

Jan,1

Feb,2

Mar,3

Apr,4

May,5

June,6

July,7

Aug,8

Sept,9

Oct,10

Nov,11

Dec,12

];

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

Load

Date,

Day(Date) As Day,

Num(Month(Date)) As Monthnumber,

Month(Date) As Month,

Year(Date) As Year,

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

// "Assignee Pblm Mgr"

;

Load

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

;

Dates:

SQL SELECT

"First Reported On" as "First Reported On",

// "Assignee Pblm Mgr"

"Problem Investigation ID" as "Ticket Number"

// "Last Modified Date" as LMD

FROM "PBM:Problem Investigation" Date

WHERE

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

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

;

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",

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

// 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')

;

PKE:

SELECT

PKE."Known Error ID" as "Ticket Number",

PKE."Submit Date",

PKE."Last Modified Date",

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

PKE."Assignee Pblm Mgr",

PKE."Assigned Group",

PKE."Submitter",

PKE."Assignee",

PKE."Product Name",

PKE."PKE_CI" as "Problem CI",

PKE."View Access",

PKE."Searchable",

PKE."Impact",

PKE."Urgency",

PKE."Priority",

PKE."Description",

PKE."Known Error Status" as "Status",

PKE."Temporary Workaround" as "Workaround",

PKE."Stastus_Reason" as "Status Reason",

PKE."Status History" as "Status History"

FROM "PBM:Known Error" PKE

WHERE

PKE."Known Error Status" NOT IN ('Closed','Cancelled')

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

;