So, I'm sorry if this is the wrong place for this post; as I am new to Qlikview; but I am so used to SQL (for Oracle) that this is going to get some getting used to although I gotta say, Qlikview Rocks!!
So I am trying to add a field which is calculated from 2 different connections.
Normally in SQL I would have joined the two tables in one connection.
Here is a simplified version of my script.
Connection 1
OLEDB CONNECT TO ...
SELECT "INCIDENT_NUMBER"||'-'||"CASE_NUMBER"||'-'||"PART_SEQUENCE" AS "Incident ID",
"REG_REPORT_DUE_ON" as "Reg Rpt Due On"
FROM CHSUSER."REGULATORY_DECISION"
WHERE "DECISION_TREE_NAME" = 'MDR'
AND "REPORT_TYPE" >0;
Connection 2
OLEDB CONNECT TO
SELECT "INCIDENT_NUMBER"||'-'||"CASE_NUMBER"||'-'||"PART_SEQUENCE" AS "Incident ID",
"INCIDENT_NUMBER"||'-'||"CASE_NUMBER"||'-'||"PART_SEQUENCE"||'-'||"REPORT_INSTANCE" AS "Source System Ref",
EXTRACTVALUE("REPORT_XML", '/eMDR/ReportDateReceivedManufacturer') as "G4 Date"
FROM CHSUSER."UNIFIED_REPORTING";
In SQL I would have done the following
Select CASE WHEN EXTRACTVALUE("REPORT_XML", '/eMDR/ReportDateReceivedManufacturer') is null then REG_REPORT_DUE_ON + 5
Ultimately I am trying to create a field called "True Due Date" which calculates 5 days past the "REG_REPORT_DUE_ON" from Connection 1 or 30 days past the "G4 Date" from Connection 2 (if it is not null).
Well, the expression that you want is probably if(IsNull([G4 Date]),[REG_REPORT_DUE_ON]+5,[G4 Date]+30); I'm not so sure how to implement that in the load script rather than in a table, though.
