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
DROP Table INCIDENTCASES;
