topic Re: Calculation between 2 tables in QlikView

Calculation between 2 tables

Fri, 26 Sep 2014 03:21:30 GMT

Hi, I need to sum up the fieldnames in red: Opening Balance and sum, but they are in different tables. How can I do it? Currently the table clbal contains all variables from bal and clbal tables. The words in blue are the common variables.

load*,date(timestamp#(Date, 'YYYYMMDDhhmm')) as DATE;

bal:

LOAD Date,

storelocation AS StoreLocation,

Opening_Balance as openingbal

FROM

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

clbal:

load *,if(peek(StoreLocation)=StoreLocation,peek(openingbal),0) as Closing_Balance,

openingbal as Opening_Balance

resident bal order by StoreLocation,Date desc;

load *,sqlDate as DATE,

sqlStoreLocation as StoreLocation,

Receipts+Returns+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.

Re: Calculation between 2 tables

jagan — Fri, 26 Sep 2014 03:44:09 GMT

Hi,

Check this script

bal:

LOAD Date,

storelocation AS StoreLocation,

Opening_Balance as openingbal

FROM

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

clbal:

load *,if(peek(StoreLocation)=StoreLocation,peek(openingbal),0) as Closing_Balance,

openingbal as Opening_Balance

resident bal order by StoreLocation,Date desc;

DROP TABLE bal;

LEFT JOIN (clbal)

load *,

sqlDate as DATE,

sqlStoreLocation as StoreLocation,

Receipts+Returns+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

FROM Table2;

Regards,

Jagan.

Re: Calculation between 2 tables

Fri, 26 Sep 2014 04:30:03 GMT

Thanks, so where do I put the script to sum up the variables?

Re: Calculation between 2 tables

jagan — Fri, 26 Sep 2014 05:19:09 GMT

Hi,

Receipts, Returns,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

are variables?

If yes then do like this

load *,

sqlDate as DATE,

sqlStoreLocation as StoreLocation,

$(Receipts)+$(Returns)+$(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

FROM TableName;

Hope this helps you.

Regards,

Jagan.

Re: Calculation between 2 tables

Fri, 26 Sep 2014 05:26:49 GMT

I meant opening balance and sum