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?
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.
Actually while this worked with two files, it doesn't seem to work with a file joined to a database.
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
VALUE as productionValue
(biff, embedded labels, table is [Export Worksheet$]);
ODBC CONNECT TO [nygrimsd;DBQ=nygrimsd] (XUserId is TdNEbTJOJLbSWZdLSLZGWXZNGLaOWPVOTLZEXYD, XPassword is McaORKNKfKYGWRdNVTbIXYMG);
SQL SELECT trade_id, value
WHERE RISK_GROUP LIKE 'DBNY_FIXED1%'
AND REPORT_NAME = 'PRICE_COB'
AND BUSINESS_DATE = '26-OCT-12'
and trade_id = 'N595709N';
LOAD TRADE_ID, Round(productionValue - value) as pvImpact
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:
can you help?
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.