Exists Function

Anonymous — Wed, 08 May 2013 16:39:33 GMT

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;

topic Exists Function in QlikView

Exists Function