Here is my scenario...I am pulling data from two systems, an old one that is going away and a new one that will eventually replace the old one. Because both systems are still running parallel, my script is set up to pull data from both into one table. When a case from the old system is coverted to a case in the new system, I no longer need to data from the old system. When a case is converted in the new system, a new INCIDENTCASEID is assigned and the field ICMSCASEID is populated with the old caseid. I am using the Exists function, but I don't think it's working correctly. All suggestions are welcomed and appreciated.
INCIDENTS2:
LOAD INCIDENTCOUNTER, INCIDENTCASEID, ICMSCASEID, INCIDENTCOUNTERID, Date(CHARGEOFFDATE,'MM-DD-YYYY') as Daily_Date, CHARGEOFFAMOUNT, CHARGEOFFGL, CHRGOFFCOSTCENTERCODE, CHARGEOFFSETRU AS LOCATIONID, CATEGORYID, TYPE2 AS FACTTYPE, SYSTEM, IMPACTEDACCOUNTNBR AS ACCOUNTNBR
FROM C:\Documents and Settings\ugtc98\Desktop\Qlikview\Pilot\QVDs\CSIINCIDENTS.QVD(qvd);
INCIDENTCASES: LOAD
CASEID AS INCIDENTCASEID, CATEGORYID, CUSTOMERACCOUNTNUMBER1 as ACCOUNTNBR
FROM C:\Documents and Settings\ugtc98\Desktop\Qlikview\Pilot\QVDs\CASES.QVD(qvd);
INCIDENTS:
INNER JOIN (INCIDENTCASES) LOAD INCIDENTCOUNTER, INCIDENTCASEID, //ICMSCASEID, INCIDENTCOUNTERID, //CATEGORYID, CHARGEOFFAMOUNT, Date(CHARGEOFFDATE,'MM-DD-YYYY') AS Daily_Date, CHARGEOFFSETRU as LOCATIONID, LOCATIONID, [PRESENTED DATE], POLICYVIOLATIONDESC, TYPE2 AS FACTTYPE, CHECKFRAUDTYPE, SYSTEM
FROM C:\Documents and Settings\ugtc98\Desktop\Qlikview\Pilot\QVDs\INCIDENTS.QVD(qvd)
WHERE CHARGEOFFDATE >= '1/1/2012';
Concatenate (INCIDENTS2)
LOAD
INCIDENTCASEID AS ICMSCASEID, *
Resident INCIDENTCASES Where not Exists (ICMSCASEID, INCIDENTCASEID); //first parameter specifies the field on which we need to check to see if there are any occurences //of the values contained in the second field
