Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How do I do derived calculations across multiple data sources?

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

1 Solution

Accepted Solutions
Not applicable
Author

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

SAMPLE.JPG

The script:

SAMPLE 2.JPG

View solution in original post

6 Replies
Not applicable
Author

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.

Not applicable
Author

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

SAMPLE.JPG

The script:

SAMPLE 2.JPG

View solution in original post

Not applicable
Author

Thanks for the help, works.

Not applicable
Author

Thanks for the help, works.

Not applicable
Author

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?

Not applicable
Author

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.