topic Re: How do I do derived calculations across multiple data sources? in QlikView

How do I do derived calculations across multiple data sources?

Tue, 30 Oct 2012 17:43:00 GMT

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

Re: How do I do derived calculations across multiple data sources?

Tue, 30 Oct 2012 18:14:26 GMT

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.

Re: How do I do derived calculations across multiple data sources?

Tue, 30 Oct 2012 18:16:29 GMT

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

The script:

Re: How do I do derived calculations across multiple data sources?

Wed, 31 Oct 2012 09:52:06 GMT

Thanks for the help, works.

Re: How do I do derived calculations across multiple data sources?

Wed, 31 Oct 2012 09:52:10 GMT

Thanks for the help, works.

Re: How do I do derived calculations across multiple data sources?

Wed, 31 Oct 2012 16:32:51 GMT

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?

Re: How do I do derived calculations across multiple data sources?

Wed, 31 Oct 2012 16:53:17 GMT

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.