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...
