topic Re: calculate from 2 tables in QlikView

calculate from 2 tables

Thu, 25 Sep 2014 05:01:14 GMT

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 — Thu, 25 Sep 2014 05:21:14 GMT

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 — Thu, 25 Sep 2014 05:37:40 GMT

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