How to Mimic Nested SELECT Statements (SQL) When Loading Data?

[conor_gallagher93]

So I thought I was being smart by trying to build a query that could combine some standard data with some aggregated figures using SQL and then using that as my SQL SELECT in the Data Load Editor, but it seems that QlikSense cannot read nested SELECT statements as I keep getting this error:

Connector reply error: ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Incorrect syntax near the keyword 'FROM'.

 Here's a sample of the code I'm trying to use:

SQL SELECT * FROM (
SELECT "EFOLDERID",
       "dtCallDate",
       "txtLOB",
       .
       .
       .
       "dtStartDate",
       "dtLPTTip"
FROM "Metastorm9"."dbo"."EOCS_Outcall_Processing") AS "Processing"
LEFT JOIN (SELECT [eFolderID],
		  [eEventTime],
		  [eActionCaption]
	   FROM [Metastorm9].[dbo].[evw_AuditTrail]
	   WHERE eActionCaption = 'Create  A Manual  Ticket') AS "Audit_Table"
ON Processing.EFOLDERID = Audit_Table.eFolderID
LEFT JOIN (SELECT [eFolderID],
		  MAX([ID]) AS Outcall_ID,
		  MAX([OutcallDate]) as MaxDate,
		  COUNT([eFolderID]) as Cases
	   FROM [Metastorm9].[dbo].[EOCS_Outcalls]
	   GROUP BY [eFolderID]) AS "Outcalls"
ON Processing.EFOLDERID = Outcalls.[eFolderID]

I guess I'm just not sure what the best way is to pull in the data. I was hoping to build the table this way as there were a few things that building this would allow me to do:

1. Some of the records from the Processing table use dtStartDate to refer to when the particular record was created, but others use the eEventTime var from the Audit_Table. Combining these variables into one table would let me create a calculated field in Qlik so that I can create one "StartTime" variable (from what I can tell, Qlik's associations do not work like joins in their ability to handle IF statements that reference data from different tables).
2. I need this single StartTime variable so that I can filter out all records that started before 2019.
3. I need to ideally link the aggregated data from Outcalls to the Processing table BEFORE I use the filter because outcalls take place after the StartTime, and I do not want to be reporting on outcalls for cases with a StartTime before 2019.

Maybe there's something I don't understand about the error, or maybe there is a Qlik solution to accomplish what I need, but I am not sure what to do at the moment and would appreciate any feedback or insights that anyone has to share!

[Lauri]

My understanding of how Qlik deals with SQL is that it sends it to the native engine (in this case, Microsoft) and lets it handle it. You got back an error from Microsoft. I suspect it's because you have a closing parenthese on this line:

FROM "Metastorm9"."dbo"."EOCS_Outcall_Processing") AS "Processing"

It's prematurely closing the statement.  

[conor_gallagher93 · Accepted solution]

Thanks for following up! Actually, I had just re-typed the code here, so that's just a typo.

Luckily, I was able to find a solution to this yesterday. From what I can tell, Qlik does not directly know how to read more advanced SQL statements; it can perform some basic SELECT and JOIN statements in your SQL statement, but I think more advanced functions need to be adapted to use Qlik's language. To that end, I was able to use the following code to achieve what I wanted:

[EOCS_Outcall_Processing_Draft]:
Load
[eFolderID],
[dtCallDate],
[txtLOB],
.
.
.
[dtStartDate],
[dtDate_Added],
[intSurveyTypeID],
[txtResponseID],
[dtSaveCurrentTime
;

SQL SELECT "EFOLDERID" AS "eFolderID",
           "dtCallDate",
    	   "txtLOB",
    	   .
    	   .
    	   .
    	   "dtStartDate",
    	   "dtDate_Added",
    	   "intSurveyTypeID",
    	   "txtResponseID",
    	   "dtSaveCurrentTime"
FROM "EOCS_Outcall_Processing";

LEFT JOIN(EOCS_Outcall_Processing_Draft)
LOAD * Resident Outcall_Summary;

LEFT JOIN(EOCS_Outcall_Processing_Draft)
LOAD * Resident Audit_Summ;

Drop Table Outcall_Summary;
Drop Table Audit_Summ;

FinalTable:
LOAD *,
If([Caption]='Create  A Manual  Ticket', date([Event_Time], 'YYYY-MM-DD hh:mm:ss'),
If([Caption]='Survey Received', date([Event_Time], 'YYYY-MM-DD hh:mm:ss'), date([dtStartDate], 'YYYY-MM-DD hh:mm:ss'))) as Case_Start_Date
Resident EOCS_Outcall_Processing_Draft
WHERE  If([Caption]='Create  A Manual  Ticket', date([Event_Time], 'YYYY-MM-DD hh:mm:ss'),
If([Caption]='Survey Received', date([Event_Time], 'YYYY-MM-DD hh:mm:ss'), date([dtStartDate], 'YYYY-MM-DD hh:mm:ss'))) >= '2019-01-01 00:00:01';

Drop Table EOCS_Outcall_Processing_Draft;

This code uses Qlik's own JOIN statements to join the EOCS_Outcall_Processing_Draft table to tables that I previously loaded in separate script pages (Outcall_Summary and Audit_Summ). The joins will connect these tables based on the columns that share the same names (in this case "eFolderID"). The RESIDENT function is what Qlik uses to call the previously loaded tables; the tutorial I read also recommended dropping them after they have been joined.

Once I have my FinalTable built, I create one last calculated field (Case_Start_Date) that picks the appropriate start time for each record, allowing me to then achieve the three objectives I laid out in my original post. Lastly, I just needed to make sure that this script appeared before the Auto-Generated Section so that I could use the table in Data Manager.