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')
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.
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,
PS
Great dreamer's dreams never fulfilled, they are always transcended. Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
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')
