topic Re: Add calculated field in QlikView

Add calculated field

Tue, 23 Apr 2013 14:31:42 GMT

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

Re: Add calculated field

Tue, 23 Apr 2013 15:02:34 GMT

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.

Re: Add calculated field

Tue, 23 Apr 2013 16:22:42 GMT

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