Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
else EXTRACTVALUE("REPORT_XML", '/eMDR/ReportDateReceivedManufacturer') + 30
end as "True Due Date"
from....
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).
Please help..
Thank you
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.
So I have gotten further; I didn't realize you could load 3 different tables from same OLEDB connection.
So here is what I need to try and accomplish:
LOAD *, if("G4 Date" is null, "Reg Rpt Due On" + 5, "G4 Date" +30);
OLEDB CONNECT TO ...
[TABLE1]:
SQL 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;
[TABLE2]:
SQL SELECT "INCIDENT_NUMBER"||'-'||"CASE_NUMBER"||'-'||"PART_SEQUENCE" AS "Incident ID",
EXTRACTVALUE("REPORT_XML", '/eMDR/ReportDateReceivedManufacturer') as "G4 Date"
FROM CHSUSER."UNIFIED_REPORTING";