How do I do derived calculations across multiple data sources?

[#1]

Hi

First time user, evaluating the product.  I am trying to import a bunch of values from two spreadsheets linked by a single key field.  I want to subtract X in source 1 from Y in source 2 to result in a derived value.  I haven't been able to find guidance on how to do this or examples.

I believe I need to do this in the load script, rather than in the GUI anywhere.  I've seen examples of how to do this when I am using one file, but not two. 

Can anyone help?

Thanks

Adam

[#2]

Here is a sample step-by-step of joining the two tables, performing the calculation, and dropping the old table. Note that in my file I am using Inline loads in order to provide a very small amount of data as a sample; yours would load from the files as usual.

[#3 · Accepted solution]

In case you are using personal edition, here is a snapshot:

The script:

[#4]

Thanks for the help, works.

[#5]

Thanks for the help, works.

[#6]

Actually while this worked with two files, it doesn't seem to work with a file joined to a database.

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';
prod:
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$]);

ODBC CONNECT TO [nygrimsd;DBQ=nygrimsd] (XUserId is TdNEbTJOJLbSWZdLSLZGWXZNGLaOWPVOTLZEXYD, XPassword is McaORKNKfKYGWRdNVTbIXYMG);
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 = 'N595709N';

derived:
LOAD  TRADE_ID, Round(productionValue - value) as pvImpact
Resident prod;

I had problems with having a name: before the ODBC keyword (as you have with TableOne in the example above).  The syntax checker did not like it.  When I run the script I get the following error:

Field not found - <value>

derived:

LOAD  TRADE_ID, Round(productionValue - value) as pvImpact

Resident prod

can you help?

[#7]

It isn't working because you are trying to tell it to pull both productionValue and value fields from the prod table, and value is not there.  Where you are pulling in trade_id and value, you must join, concatenate, etc. in order to get it in to the prod table.  One other thing to note is that everything is case-sensitive.  I noticed that you have TRADE_ID and trade_id.