Solved: Using two select statement in the data laod - Page 2 - Qlik Community

Report Inappropriate Content · ‎2017-02-06

I am trying to query same database but different tables using SQL language with two SELECT statement from the data load editor but it keeps giving me error. But my query separately when tested on my SQL developer?? Does qlik sense not allow using more than one Table.

Report Inappropriate Content · ‎2017-02-06

That link works when used on a table. The query was running perfect until I added the first SELECT. when I comment out the first SELECT statement it works fine.

I added the first table, because I have seen people do and it works And Avinash, I don't really get what u said??

rahulpawarb · ‎2017-02-06

Hello Victor,

Below are series of steps needs to be followed to get data from a RDBMS ( I trust that you have followed the same; these are for validation purpose):

1. Select the connection type/connector from database section in edit script e.g. ODBC or OLEDB

2. Click of connect button.

3. if ODBC selected then select the Data Source and enter User Id & Password

4. if OLEDB selected then select the provider and enter the connection details (Server, user Id, Password, Database)

5. Test the connection and hit ok. This will create connect statement in script

6. Use Select button to load specific table or else you can use your custom SQL.

7. Append SQL before SELECT and assign logical table name.

I hope that you have followed these steps.

Regards!

Rahul

avinashelite · ‎2017-02-06

could you please share the complete script what you have written ??

have you tested the DB connection

Report Inappropriate Content · ‎2017-02-06

This I have followed perfectly. Like I said, it was working perfect until I added the first SELECT statement to my script then it started given me error, when I comment out the first SELECT statement, it works perfectly. But both SELECT works perfectly on my SQL developer.

Report Inappropriate Content · ‎2017-02-06

Yes the DB connection works well and If I take the first SELECT statement off, it works perfectly. Below is the full script.

SELECT *
FROM ARADMIN."VOC_FEEDBACK_V2_VIEW"
WHERE CREATE_DATE > 18000 AND YEAR > 2016

    AND CATEGORY in(
'ARCHIVE QUICK CHECK',
'CLINICAL DATA VISIBILITY TOOL',
'CLIN PHARM MODELING SIMULATION ANALYSIS WORKFLOW',
'CPMS AW',
'DATASET MANAGER',
'EDX - ELECTRONIC DOC EXCHANGE',
'ENAVIGATOR',
'GSP - GSK STUDY PORTAL',
'HARP',
'HARP SUITE',
'HIF - HEALTHCARE INFO FACTORY',
'HEALTHCARE INFO FACTORY VISUALISATION',
'INET CATALIST',
'INSIGHT (STUDY MANAGER)',
'PREDICTIVE MODELLING',
'PREDICTIVE MODELLING ENVIRONMENT (PME)',
'RBM - RISK BASED MONITORING',
'SITE OPTIMIZER',
'SPECTRE',
'STUDY ALLOCATION AND RESOURCING SYSTEM (STARS)',
'STUDY OPTIMIZER',
'SUPPLIER CONTRACTS',
'TSCG - TIBCO SPOTFIRE CLINICAL GRAPHICS',
'TRANSFER OF OBLIGATIONS')

    AND RA in ('RD-IT-SUPPORT-SATYAM',
                                      'IT-RDIT-SUPPORT',
                                      'RD-IT-SUPPORT-WIPRO',
                                      'List of RAs');
SELECT TICKET_ID_
      ,concat('https://vocfeedback.gsk.com/IM_view/ticketInfo.asp?ticket_id=',TICKET_ID_) as Ticket_Link
      ,DECODE (STATUS ,'0', 'NEW'
                      ,'1', 'ASSIGNED'
                      ,'2', 'WORK IN PROGRESS'
                      ,'3', 'HOLD'
                      ,'4', 'RESOLVED'
                      ,'5', 'CLOSED') TICKET_STATUS
      ,REQUESTER_LOGIN_NAME_
      ,REQUESTER_NAME_
      ,EMAIL
      ,LOCATION
      ,SITE
      ,HOURS_TO_RESOLVE
      ,Country
      ,SUBMITTED_BY
      ,SUBMITTER_GRP
      ,ASSIGNED_TO_GROUP_ AS Resolving_Agency
      ,DECODE (SOURCE ,0, 'Phone'
                      ,1, 'PhoneMail'
                      ,2, 'Email'
                      ,3, 'Web'
                      ,4, 'Auto'
                      ,5, 'External DSO'
                      ,6, 'Client Chat') Source
      ,Type
      ,CATEGORY AS Ticket_Category
      ,DECODE (CATEGORY ,'ARCHIVE QUICK CHECK', 'SILVER'
                        ,'CLINICAL DATA VISIBILITY TOOL', 'SILVER'
                        ,'CLIN PHARM MODELING SIMULATION ANALYSIS WORKFLOW', 'SILVER'
                        ,'CPMS AW', 'GOLD'
                        ,'DATASET MANAGER', 'SILVER'
                        ,'EDX - ELECTRONIC DOC EXCHANGE', 'GOLD'
                        ,'ENAVIGATOR', 'SILVER'
                        ,'GSP - GSK STUDY PORTAL', 'GOLD'
                        ,'HARP', 'GOLD'
                        ,'HARP SUITE', 'GOLD'
                        ,'HIF - HEALTHCARE INFO FACTORY', 'SILVER'
                        ,'HEALTHCARE INFO FACTORY VISUALISATION', 'SILVER'
                        ,'INET CATALIST', 'SILVER'
                        ,'INSIGHT (STUDY MANAGER)', 'SILVER'
                        ,'PREDICTIVE MODELLING', 'SILVER'
                        ,'PREDICTIVE MODELLING ENVIRONMENT (PME)', 'SILVER'
                        ,'RBM - RISK BASED MONITORING', 'SILVER'
                        ,'SITE OPTIMIZER', 'SILVER'
                        ,'SPECTRE', 'SILVER'
                        ,'STUDY ALLOCATION AND RESOURCING SYSTEM (STARS)', 'SILVER'
                        ,'STUDY OPTIMIZER', 'SILVER'
                        ,'SUPPLIER CONTRACTS', 'SILVER'
                        ,'TSCG - TIBCO SPOTFIRE CLINICAL GRAPHICS', 'SILVER'
                        ,'TRANSFER OF OBLIGATIONS', 'SILVER') AS Tier
      ,SUB_CODE
      ,OPENING_TYPE
      ,OPENING_CATEGORY
      ,OPENING_SUB_CODE
      ,PRIORITY AS Priority_Number
      ,DECODE (PRIORITY      ,'0' , 'Low'
                             ,'1' , 'Medium'
                             ,'2' , 'High'
                             ,'3' , 'Urgent'
                             ,'4' , 'Critical') as Decode_Priority
   ,DECODE (SAP_MET_INITIAL_RESPONSE_D_T_ ,0,'Yes'
                                          ,1,'No') AS Responded_within_SLA
      ,Decode(case_type ,0,'Incident'
                        ,1,'Request'
                        ,2,'Request')AS Case_Type
      ,ASSIGNED_TO_INDIVIDUAL_ AS Analyst
      ,ASSIGNEE_LOGIN_NAME AS Analyst_ID
      ,(RESOLVE_BEFORE-CREATE_TIME)/60/60/24 AS RESOLVE_BEFORE
      ,ARADMIN.GET_REM_DATE(ARRIVAL_TIME)AS ARRIVAL_DATE
      ,TO_CHAR(TO_DATE(ARADMIN.GET_REM_DATE(ARRIVAL_TIME), 'MONTH DD, YYYY HH12:MI:SSAM'), 'YYYY') AS Arrival_YEAR
      ,TO_CHAR(TO_DATE(ARADMIN.GET_REM_DATE(ARRIVAL_TIME), 'MONTH DD, YYYY HH12:MI:SSAM'), 'MON') AS Arrival_MONTH_
      ,TO_CHAR(TO_DATE(ARADMIN.GET_REM_DATE(ARRIVAL_TIME), 'MONTH DD, YYYY HH12:MI:SSAM'), 'MONTH') AS Arrival_MONTH
      ,TO_CHAR(TO_DATE(ARADMIN.GET_REM_DATE(ARRIVAL_TIME), 'MONTH DD, YYYY HH12:MI:SSAM'), 'Mon-YY') AS Arrival_Time
      ,ARADMIN.GET_REM_DATE(CREATE_TIME)AS CREATE_DATE
      ,ARADMIN.GET_REM_DATE(RESOLVED_TIME) AS RESOLVED_TIME
      ,TO_CHAR(TO_DATE(ARADMIN.GET_REM_DATE(RESOLVED_TIME), 'MONTH DD, YYYY HH12:MI:SSAM'), 'YYYY') AS Resolve_YEAR
      ,TO_CHAR(TO_DATE(ARADMIN.GET_REM_DATE(RESOLVED_TIME), 'MONTH DD, YYYY HH12:MI:SSAM'), 'MON') AS Resolve_MONTH_
      ,TO_CHAR(TO_DATE(ARADMIN.GET_REM_DATE(RESOLVED_TIME), 'MONTH DD, YYYY HH12:MI:SSAM'), 'MONTH') AS Resolve_MONTH
      ,TO_CHAR(TO_DATE(ARADMIN.GET_REM_DATE(RESOLVED_TIME), 'MONTH DD, YYYY HH12:MI:SSAM'), 'Mon-YY') AS Resolve_Time
      ,ARR____RESOLVED_TIME AS "Arrival to Resolve Seconds"
      ,TO_CHAR(ARR____RESOLVED_TIME / 60, '99,999.99')AS "Arrival to Resolve Minutes"
      ,TO_CHAR(ARR____RESOLVED_TIME / 60 / 60, '99,999.99')AS "Arrival to Resolve Hours"
      ,TO_CHAR(ARR____RESOLVED_TIME / 60 / 60 / 8, '99,999.99')AS "Arrival to Resolve Days"
      ,Decode(EL_3_ ,0,'No'
                    ,1,'Yes')AS Remedy_SLA_Met
      ,MISSED_SLA_REASON
      ,ROOT_CAUSE
      ,Solution_Text
      ,ARADMIN.GET_REM_DATE(Last_Updated)AS Last_Updated
      ,Updated_by
      ,No_of_Client_Chase
      ,No_of_Updates
      ,TO_CHAR(Arr___1st_WIP / 60 / 60, '99,999.99')AS "Arr___1st_WIP Hours"
      ,TO_CHAR(Time_To_Accept / 60 / 60, '99,999.99')AS "Time_To_Accept Hours"
      ,TO_CHAR(Ass___Resolve_Time / 60 / 60, '99,999.99')AS "Ass___Resolve_Time Hours"
      ,TO_CHAR(A1ST_WIP___Resolve / 60 / 60, '99,999.99')AS "A1ST_WIP___Resolve Hours"
      ,No__of_Children
      ,Decode(Is_this_a_Child__ ,0,'Yes' ,1,'No')Is_this_a_Child__
      ,Decode(Parent__ ,0,'Yes' ,1,'No')Parent__
      ,Decode(closed_First_Contact ,Null,'No' ,0,'Yes')closed_First_Contact
      ,Group_Count
      ,Hold_Started_At
      ,HOLD_UNTIL
      ,HOLD_REASON
      ,Total_Hold
      ,SUMMARY
      ,Decode(INCIDENT_MGMT_QUALITY_ISSUE ,0,'Misrouted Incident'
                                          ,1,'Quality of Information'
                                          ,2,'Previous Grp Should Resolve')AS INCIDENT_MGMT_QUALITY_ISSUE
      ,NO_OF_QUALITY_ISSUES
      ,ARADMIN.GET_REM_DATE(Sap_Actual_Response_Date_Time)AS Sap_Actual_Response_Date_Time
      ,(Sap_Actual_Response_Date_Time - ARRIVAL_TIME)/60/60 as "Response Duration"
      ,Decode(Set_on_Resolved_ ,null,'No' ,0,'Yes')Set_on_Resolved_
      ,Decode(Reactivated_ ,0,'No' ,1,'Yes')Reactivated_
      ,ARADMIN.GET_REM_DATE(Reactivated_Date)AS Reactivated_Date
      ,keyword
      ,ASSIGNED_TO_GROUP_ As RA_Cat
      --,INSTR('work_log', 'acknowledge') as "Acknowledged"

FROM INCIDENT_MANAGEMENT

where to_date(ARADMIN.get_rem_date(CREATE_TIME), 'Month DD, YYYY HH:MI:SSAM') > to_date('01/01/2016', 'MM/DD/YYYY')
and to_date(ARADMIN.get_rem_date(CREATE_TIME), 'Month DD, YYYY HH:MI:SSAM') < to_date('01/01/2018', 'MM/DD/YYYY')

AND (ASSIGNED_TO_GROUP_ = 'RD-IT-SUPPORT-SATYAM' OR ASSIGNED_TO_GROUP_ = 'IT-RDIT-SUPPORT' OR ASSIGNED_TO_GROUP_ = 'RD-IT-SUPPORT-WIPRO')

AND CATEGORY in(
'ARCHIVE QUICK CHECK',
'CLINICAL DATA VISIBILITY TOOL',
'CLIN PHARM MODELING SIMULATION ANALYSIS WORKFLOW',
'CPMS AW',
'DATASET MANAGER',
'EDX - ELECTRONIC DOC EXCHANGE',
'ENAVIGATOR',
'GSP - GSK STUDY PORTAL',
'HARP',
'HARP SUITE',
'HIF - HEALTHCARE INFO FACTORY',
'HEALTHCARE INFO FACTORY VISUALISATION',
'INET CATALIST',
'INSIGHT (STUDY MANAGER)',
'PREDICTIVE MODELLING',
'PREDICTIVE MODELLING ENVIRONMENT (PME)',
'RBM - RISK BASED MONITORING',
'SITE OPTIMIZER',
'SPECTRE',
'STUDY ALLOCATION AND RESOURCING SYSTEM (STARS)',
'STUDY OPTIMIZER',
'SUPPLIER CONTRACTS',
'TSCG - TIBCO SPOTFIRE CLINICAL GRAPHICS',
'TRANSFER OF OBLIGATIONS')

avinashelite · ‎2017-02-06

add the SQL statement before both the SELECT statement

rahulpawarb · ‎2017-02-06

Hello Victor,

Could you please share the error screenshot &application along with sample data? This will help us to identify the root cause.

Regards!

Rahul

sdmech81 · ‎2017-02-06

U have to add SQL statement for sql query before u use it in QLIKVIEW byddy..

Plss do tht..It will work..

Sachin

sdmech81 · ‎2017-02-06

Say something like below

Temporary table name(Give some name) :

SQL

Then paste ur ql query here..

Sachin

Report Inappropriate Content · ‎2017-02-06

Hi Avinash,

Can u show me what you mean by using the script I shared pls,?? Delete most fields to shorten it.