How can I join two tables but have a log of records which don't exist in one set?
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>
LOAD distinct TRADE_ID, Round(productionValue - VALUE) as valueImpact