calculate from 2 tables

[#1]

Hi, I need to sum up the 2 fields in red: opening balance and sum. Since they are from different tables, how can I do it?

load *,date(floor(timestamp#(Replace(Date,' ',' '), 'YYYYMMDDhhmm'))) as DATERANGE;

bal:

LOAD Date,

     location as LOCATION,

     Opening_Balance

FROM

(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

clbal:

load *,

if(peek(location)=location,peek(Opening_Balance),0) as ClosingBalance,

//peek(Opening_Balance) as Closing_Balance,

peek(location) as NextRowStore

resident bal order by location,Date desc;

opbaltrans:

load *, Receipts+Returns+non_new_transfer_in+non_new_transfer_out+transfer_out+transfer_in+issue_from_store+

intmxmp+return_to_store+total_receipts_returns+stock_adjustments+adjustments_to_current_balance+

loan_transfer_out+loan_transfer_in as sum;

Thanks.

[sundarakumar]

Two options are there

1. Find the primary key and join it in the back end, do resident and sum up.

2. find the perfect key to link them and do the sum in front end i.e. in the chart itself. If u a have the assocaition correct it can be done..

-Sundar

[jyothish8807]

Hi Rachel,

Over here i cant see any primarykey joining both the tables.

You should have primary key joining both the table, then you can join them using any join if needed and create a custom fiels using resident table.

Regards

KC