1 Reply Latest reply: Nov 2, 2012 11:48 AM by Stefan Wühl RSS

    How can I join two tables but have a log of records which don't exist in one set?

      Hello,

       

      I am trying to import data from two sources, compare values where keys match, but also report on fields where there was no matching key.

       

      So, let's say I have two fields TRADE_ID and VALUE, and I have data like the below

       

      Table LEFT: [123, 1], [124, 2], [125, 3]

      Table RIGHT: [123,2], [124,0]

       

      I want to have an object that shows me that there are value differences for 123 and 124, and a seperate object that shows me 125 is missing from the RIGHT.

       

      If I do a JOIN (INNER or OUTER) when loading the second table (Right), then I am able to reference Left after the join as a resident table.  However I am unable to reference Right - I get table not found.  that means I can achieve my objective to compare, but not report on missing records.

       

      From reading the documentation it sounds like I need to use KEEP, but I do that my script fails to find the field VALUE, implying something has gone wrong.  I've pasted the latest code I'm struggling with below:

       

       

      SET ThousandSep=',';
      SET DecimalSep='.';
      SET MoneyThousandSep=',';
      SET MoneyDecimalSep='.';
      SET MoneyFormat='£#,##0.00;-£#,##0.00';
      SET TimeFormat='hh:mm:ss';
      SET DateFormat='DD/MM/YYYY';
      SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
      SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
      SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

      ODBC CONNECT TO [nygrimsd;DBQ=nygrimsd] (XUserId is TdNEbTJOJLbSWZdLSLZGWXZNGLaOWPVOTLZEXYD, XPassword is McaORKNKfKYGWRdNVTbIXYMG);

      left:
      LOAD TRADE_ID,
           VALUE as productionValue
      FROM
      [\\dbg.ads.db.com\LON-USERS-U\VF01_USERS01\smitada\config\Desktop\export1.xls]
      (biff, embedded labels, table is [Export Worksheet$]);

      right:
      INNER KEEP SQL SELECT TRADE_ID, VALUE
      FROM VERTEX_REPORT.REPORT
      WHERE RISK_GROUP LIKE 'DBNY_FIXED1%'
      AND REPORT_NAME = 'PRICE_COB'
      AND BUSINESS_DATE = '26-OCT-12'
      AND TRADE_ID IN(
      'N594682N'
      ,'N594932N'
      ,'N595080N'
      ,'N595130N'
      ,'N595255N'
      ,'N595377N'
      ,'N595385N'
      ,'N595393N'
      ,'N1263110N'
      ,'N1263562N');


      derived:
      LOAD distinct TRADE_ID, Round(productionValue - VALUE) as valueImpact
      Resident left;
      trace counting rows;
      $c = (NoOfRows (left));
      trace starting loop;
      for n=0 to 10
      if(exists(TRADE_ID, peek(left, n))) then
        trace TRADE_ID exists in left;
        else
        trace TRADE_ID does not exist in left;
      endif
      next

       

      Here's the error I get

      Field not found - <VALUE>

      derived:

      LOAD distinct TRADE_ID, Round(productionValue - VALUE) as valueImpact

      Resident left

       

      Thanks in advance for any help...