topic Re: Using two select statement in the data laod in QlikView

Using two select statement in the data laod

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Using two select statement in the data laod

vinieme12 — Mon, 06 Feb 2017 11:28:15 GMT

what is the error?

also post your SQL statement

Re: Using two select statement in the data laod

Mon, 06 Feb 2017 11:36:11 GMT

Its a connector error problem.

Cant find the Attach button for a document, but the below is my script:

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

Re: Using two select statement in the data laod

avinashelite — Mon, 06 Feb 2017 11:38:03 GMT

Could you share the complete load script statements

Re: Using two select statement in the data laod

avinashelite — Mon, 06 Feb 2017 11:39:47 GMT

are you able to select the 'https://vocfeedback.gsk.com/IM_view/ticketInfo.asp?ticket_id=',TICKET_ID_) from QV ? does it has access for QS?

Re: Using two select statement in the data laod

Mon, 06 Feb 2017 11:40:22 GMT

That's how it is on my script with only a few field names removed to shorten the code.

Re: Using two select statement in the data laod

Mon, 06 Feb 2017 11:42:49 GMT

there is a field name before that line called TICKET_ID and a function CONCAT before the link.

Re: Using two select statement in the data laod

sdmech81 — Mon, 06 Feb 2017 11:47:52 GMT

Have u put SQL statement before select??

Re: Using two select statement in the data laod

Mon, 06 Feb 2017 11:49:25 GMT

Nope. After the connector string, then my SELECT statement.

Re: Using two select statement in the data laod

avinashelite — Mon, 06 Feb 2017 11:50:41 GMT

Try like this

*Run the above complete query in your DB

*If it runs success then 'https://vocfeedback.gsk.com/IM_view/ticketInfo.asp?ticket_id=',TICKET_ID_

try to load the data separately from this link

I feel your DB is not able to fetch the data from the above link , the statements after SELECT * will execute first in DB and then it returns to QS so issue is not with QS

Hope this helps you

Re: Using two select statement in the data laod

Mon, 06 Feb 2017 11:57:10 GMT

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??

Re: Using two select statement in the data laod

rahulpawarb — Mon, 06 Feb 2017 12:03:49 GMT

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

Re: Using two select statement in the data laod

avinashelite — Mon, 06 Feb 2017 12:07:02 GMT

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

have you tested the DB connection

Re: Using two select statement in the data laod

Mon, 06 Feb 2017 12:08:55 GMT

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.

Re: Using two select statement in the data laod

Mon, 06 Feb 2017 12:10:56 GMT

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"

Re: Using two select statement in the data laod

avinashelite — Mon, 06 Feb 2017 12:15:09 GMT

add the SQL statement before both the SELECT statement

Re: Using two select statement in the data laod

rahulpawarb — Mon, 06 Feb 2017 12:16:15 GMT

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

Re: Using two select statement in the data laod

sdmech81 — Mon, 06 Feb 2017 12:17:44 GMT

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

Plss do tht..It will work..

Sachin

Re: Using two select statement in the data laod

sdmech81 — Mon, 06 Feb 2017 12:20:24 GMT

Say something like below

Temporary table name(Give some name) :

SQL

Then paste ur ql query here..

Sachin

Re: Using two select statement in the data laod

Mon, 06 Feb 2017 12:21:53 GMT

Hi Avinash,

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