Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kasimyc
Contributor III
Contributor III

Convert SQL query into Qlik Sense

Hi guys,
I want to convert the SQL query I mentioned below into a format that qlik sense can understand. Can you help me with this?

Thanks ALL

"

SELECT
ODS.ddd_ARC_TABLE1_EXTERNAL_ID3,
ODS.ddd_ARC_TABLE1_EXTERNAL_ID5,
ODS.ddd_ARC_TABLE1_NAME,
ODS.ddd_ARC_TABLE2BATCH_ID,
CUSTCALL.MAX_CALL_DATE,
CUSTCALL.MIN_CALL_DATE,
ODS.ddd_ARC_TABLE2LIST_ID,
COUNT(ODS.ddd_ARC_TABLE2CUST_KEY),
TRUNC(ODS.ddd_ARC_BATCH_MASTER.IMPORT_START_DATE),
COUNT(ODS.ddd_ARC_TABLE2CUST_KEY) - COUNT(CASE WHEN PREDIAL.RESULT IN ('FLAG 1 NO','FLAG 2 NO') THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN PREDIAL2.RESULT IN ('FLAG 1 NO','FLAG 2 NO','IP VAR,KVKK YOK') THEN ODS.ddd_ARC_TABLE2CUST_KEY END) ,
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (12) THEN ODS.ddd_ARC_TABLE2CUST_KEY END) - COUNT(CASE WHEN PREDIAL.RESULT IN ('FLAG 1 NO','FLAG 2 NO') THEN ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (1,8,9) AND ODS.ddd_ARC_TABLE2CALL_COUNT = 0 THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE NOT IN (1,8,9) THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE NOT IN (1,8,9) AND ODS.ddd_ARC_TABLE2RES_REACHED = 1 THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
CASE WHEN COUNT(ODS.ddd_ARC_TABLE2CUST_KEY) - COUNT(CASE WHEN PREDIAL.RESULT IN ('FLAG 1 NO','FLAG 2 NO') THEN ODS.ddd_ARC_TABLE2CUST_KEY END) = 0 THEN 0
ELSE CAST(COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE NOT IN (1,8,9) AND ODS.ddd_ARC_TABLE2RES_REACHED = 1 THEN ODS.ddd_ARC_TABLE2CUST_KEY END) AS FLOAT)
/ CAST(COUNT(ODS.ddd_ARC_TABLE2CUST_KEY) - COUNT(CASE WHEN PREDIAL.RESULT IN ('FLAG 1 NO','FLAG 2 NO') THEN ODS.ddd_ARC_TABLE2CUST_KEY END) AS FLOAT) * 100
END ,
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RES_IS_SUCCESSFUL = 1 THEN ODS.ddd_ARC_TABLE2CUST_KEY END) ,
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (7) THEN ODS.ddd_ARC_TABLE2CUST_KEY END) ,
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (1,8,9) THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (9) AND ODS.ddd_ARC_TABLE2LAST_AGENT_ID = 'DIALER' THEN ODS.ddd_ARC_TABLE2CUST_KEY END) ,
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (8,9) AND ODS.ddd_ARC_TABLE2LAST_AGENT_ID <> 'DIALER' THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RES_CTI_ID IN (11,12) THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (3) THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (1,8,9) AND ODS.ddd_ARC_TABLE2CALL_COUNT = 1 THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (1,8,9) AND ODS.ddd_ARC_TABLE2CALL_COUNT = 2 THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (1,8,9) AND ODS.ddd_ARC_TABLE2CALL_COUNT = 3 THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (1,8,9) AND ODS.ddd_ARC_TABLE2CALL_COUNT = 4 THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2CALL_COUNT = 5 THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2CALL_COUNT > 5 THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RES_EXPLANATION = 'CANCEL' THEN ODS.ddd_ARC_TABLE2CUST_KEY END),
TO_DATE(ODS.ddd_ARC_TABLE2EXPIRE_DATE)-TRUNC(CURRENT_DATE),
NVL(BATCH_DETAILS2.IMPORT_EDILEMEYEN,0)
FROM
ODS.ddd_ARC_BATCH_MASTER RIGHT OUTER JOIN ODS.ddd_ARC_CDS ON (ODS.ddd_ARC_TABLE2BATCH_ID=ODS.ddd_ARC_BATCH_MASTER.BATCH_ID)
LEFT OUTER JOIN (
SELECT
ODS.ddd_ARC_LOGDIAL.CUST_KEY ,
ODS.ddd_ARC_LOGDIAL.RESULT
FROM
ODS.ddd_ARC_LOGDIAL
WHERE
ODS.ddd_ARC_LOGDIAL.RESULT <> 'Allowed'
AND
ODS.ddd_ARC_LOGDIAL.ID IN (SELECT MAX(ODS.ddd_ARC_LOGDIAL.ID) FROM ODS.ddd_ARC_LOGDIAL WHERE ODS.ddd_ARC_LOGDIAL.RESULT <> 'Allowed' GROUP BY CUST_KEY)

) PREDIAL ON (PREDIAL.CUST_KEY=ODS.ddd_ARC_TABLE2CUST_KEY)
LEFT OUTER JOIN (
SELECT
ODS.ddd_ARC_CUSTCALL.BATCH_ID,
MIN( ODS.ddd_ARC_CUSTCALL.CALL_DATE) AS MIN_CALL_DATE,
MAX(ODS.ddd_ARC_CUSTCALL.CALL_DATE) AS MAX_CALL_DATE
FROM ODS.ddd_ARC_CUSTCALL
GROUP BY ODS.ddd_ARC_CUSTCALL.BATCH_ID
) CUSTCALL ON (CUSTCALL.BATCH_ID=ODS.ddd_ARC_TABLE2BATCH_ID)
LEFT OUTER JOIN (
SELECT
ODS.ddd_ARC_LOGDIAL.CUST_KEY,
ODS.ddd_ARC_LOGDIAL.RESULT
FROM ODS.ddd_ARC_LOGDIAL
WHERE ODS.ddd_ARC_LOGDIAL.RESULT IN ('FLAG 1 NO','FLAG 2 NO')
AND ID IN (SELECT MAX(ODS.ddd_ARC_LOGDIAL.ID) FROM ODS.ddd_ARC_LOGDIAL
WHERE ODS.ddd_ARC_LOGDIAL.RESULT IN ('FLAG 1 NO','FLAG 2 NO') GROUP BY ODS.ddd_ARC_LOGDIAL.CUST_KEY)
) PREDIAL2 ON (PREDIAL2.CUST_KEY=ODS.ddd_ARC_TABLE2CUST_KEY)
LEFT OUTER JOIN (
SELECT BATCH_ID, COUNT(*) AS IMPORT_EDILEMEYEN
FROM ODS.ddd_ARC_BATCH_DETAILS
WHERE STATUS = 2
GROUP BY BATCH_ID
) BATCH_DETAILS2 ON (BATCH_DETAILS2.BATCH_ID=ODS.ddd_ARC_TABLE2BATCH_ID)
WHERE
ODS.ddd_ARC_TABLE2ENABLED IN ( 1 )
GROUP BY
ODS.ddd_ARC_TABLE1_EXTERNAL_ID3,
ODS.ddd_ARC_TABLE1_EXTERNAL_ID5,
ODS.ddd_ARC_TABLE1_NAME,
ODS.ddd_ARC_TABLE2BATCH_ID,
CUSTCALL.MAX_CALL_DATE,
CUSTCALL.MIN_CALL_DATE,
ODS.ddd_ARC_TABLE2LIST_ID,
TRUNC(ODS.ddd_ARC_BATCH_MASTER.IMPORT_START_DATE),
TO_DATE(ODS.ddd_ARC_TABLE2EXPIRE_DATE)-TRUNC(CURRENT_DATE),
NVL(BATCH_DETAILS2.IMPORT_EDILEMEYEN,0)   "

9 Replies
PrashantSangle

try using this query directly in qlik.

 

Great dreamer's dreams never fulfilled, they are always transcended.
ashok1203
Creator II
Creator II

try using the same query in qlik, by mentioning SQL at starting position

Ex: SQL query

AAK
kasimyc
Contributor III
Contributor III
Author

Unfortunately, it didn't work. I got an error for the date format, I also got an error for "invalid number of arguments". In the code snippet, there are terms / uses that qliksense does not accept. For this reason, I could not find exactly where I was hanging out. I would be grateful if you help

kasimyc
Contributor III
Contributor III
Author

Hi PrashantSangle, I tried this solution, but it didn't work. I got an error ora-00909 and date format. How can I make all of the code blog that qliksens can interpret? Is there a tool for this?

thank you for the answer

ArnadoSandoval
Specialist II
Specialist II

Hi @kasimyc 

Would you be able to create a view out of your query in Oracle?  I looked at  your query yesterday finding too many columns missing aliases, but if this query run in Oracle, then create a view as suggested and consume the view in Qlik.

Hope this helps,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
kasimyc
Contributor III
Contributor III
Author

Hi @ArnadoSandoval,

Can you tell me which is the field you identified as the missing field? In the query, I have specified the names of some fields as dummy. Have you spotted a problem due to this, I'm asking to be sure?

thanx 

kasimyc
Contributor III
Contributor III
Author

Untitled.png

 

After the change I made for the error I received in date format, I now get an error of argument number. If I fix this I don't know what the next error will be 🙂

Thanks for your help. But I haven't solved the problem yet

ArnadoSandoval
Specialist II
Specialist II

Hi @kasimyc 

Two days ago I dived into your query, the first thing I tried was to understand it logic and added some indentation (queries this large without indentation are confusing to be mild) anyhow, below I am showing all the SELECT fields up to the FROM statement

SELECT
       ODS.ddd_ARC_TABLE1_EXTERNAL_ID3
     , ODS.ddd_ARC_TABLE1_EXTERNAL_ID5
     , ODS.ddd_ARC_TABLE1_NAME
     , ODS.ddd_ARC_TABLE2BATCH_ID
     , CUSTCALL.MAX_CALL_DATE
     , CUSTCALL.MIN_CALL_DATE
     , ODS.ddd_ARC_TABLE2LIST_ID
     , COUNT(ODS.ddd_ARC_TABLE2CUST_KEY)
     , TRUNC(ODS.ddd_ARC_BATCH_MASTER.IMPORT_START_DATE)
     , COUNT(ODS.ddd_ARC_TABLE2CUST_KEY) - COUNT(CASE WHEN PREDIAL.RESULT IN ('FLAG 1 NO','FLAG 2 NO') THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN PREDIAL2.RESULT IN ('FLAG 1 NO','FLAG 2 NO','IP VAR,KVKK YOK') THEN ODS.ddd_ARC_TABLE2CUST_KEY END) 
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (12) THEN ODS.ddd_ARC_TABLE2CUST_KEY END) - COUNT(CASE WHEN PREDIAL.RESULT IN ('FLAG 1 NO','FLAG 2 NO') THEN ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (1,8,9) AND ODS.ddd_ARC_TABLE2CALL_COUNT = 0 THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE NOT IN (1,8,9) THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE NOT IN (1,8,9) AND ODS.ddd_ARC_TABLE2RES_REACHED = 1 THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , CASE WHEN COUNT(ODS.ddd_ARC_TABLE2CUST_KEY) - COUNT(CASE WHEN PREDIAL.RESULT IN ('FLAG 1 NO','FLAG 2 NO') THEN ODS.ddd_ARC_TABLE2CUST_KEY END) = 0 THEN 0
ELSE CAST(COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE NOT IN (1,8,9) AND ODS.ddd_ARC_TABLE2RES_REACHED = 1 THEN ODS.ddd_ARC_TABLE2CUST_KEY END) AS FLOAT) / CAST(COUNT(ODS.ddd_ARC_TABLE2CUST_KEY) - COUNT(CASE WHEN PREDIAL.RESULT IN ('FLAG 1 NO','FLAG 2 NO') THEN ODS.ddd_ARC_TABLE2CUST_KEY END) AS FLOAT) * 100
END 
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RES_IS_SUCCESSFUL = 1 THEN ODS.ddd_ARC_TABLE2CUST_KEY END) 
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (7) THEN ODS.ddd_ARC_TABLE2CUST_KEY END) 
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (1,8,9) THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (9) AND ODS.ddd_ARC_TABLE2LAST_AGENT_ID = 'DIALER' THEN ODS.ddd_ARC_TABLE2CUST_KEY END) 
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (8,9) AND ODS.ddd_ARC_TABLE2LAST_AGENT_ID <> 'DIALER' THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RES_CTI_ID IN (11,12) THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (3) THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (1,8,9) AND ODS.ddd_ARC_TABLE2CALL_COUNT = 1 THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (1,8,9) AND ODS.ddd_ARC_TABLE2CALL_COUNT = 2 THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (1,8,9) AND ODS.ddd_ARC_TABLE2CALL_COUNT = 3 THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RECORD_STATE IN (1,8,9) AND ODS.ddd_ARC_TABLE2CALL_COUNT = 4 THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2CALL_COUNT = 5 THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2CALL_COUNT > 5 THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , COUNT(CASE WHEN ODS.ddd_ARC_TABLE2RES_EXPLANATION = 'CANCEL' THEN ODS.ddd_ARC_TABLE2CUST_KEY END)
     , TO_DATE(ODS.ddd_ARC_TABLE2EXPIRE_DATE)-TRUNC(CURRENT_DATE)
     , NVL(BATCH_DETAILS2.IMPORT_EDILEMEYEN,0)
FROM
--
  • All the COUNT() fields do not have alias, so no column name, e.g. no field name.
  • Near the end, you got a Date difference without alias, e.g. no column name.
  • Of course, Qlik may generate their column names randomly, but why, it is the developer responsibility to properly identify the information returned by the query.

My suggestion for a view is based on these points:

  • The columns will receive a name, for the world to know (the world is your using community consuming your solution)
  • Oracle will do a better job resolving the query (it is not that Qlik can't); and the Oracle's team will keep ownership of its logic, so future updates to the schema will propagate to this view; if you don't take this approach, you are adding an extra level of difficulty to you solution.
  • We do not implement Qlik dashboards and applications by running database queries to collect the data to be presented by the Qlik UI via a dashboard or application; with Qlik, we prepared a data model, in QVDs who are a direct representation of the backend database, we refresh these data model daily.

Hope this helps,

 

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
kasimyc
Contributor III
Contributor III
Author

I will also try loading tables on qlik. Thank you