How to do cross-table calculations

Thu, 23 Apr 2015 10:52:33 GMT

Hello,

I'm rather new to Qlikview and I am encountering a problem which I can't solve. So hopefully someone can shed a light on it.

I have three different tables which may not be joined together (that would generate a lot of $Syn.. tables)

How do I make calculations with items which are stored in different tables in script?

Example:

rangemax(pramount, 0) /rangemax(PRCurrExRate,0) as PR_USD,

rangemax(poamount, 0) /rangemax(POCurrExRate,0) as PO_USD,

rangemax(npoamount, 0)/rangemax(NPOCurrExRate,0) as NPO_USD

PO_USD+NPO_USD as po_npo_USD,

(rangemax(PO_USD, 0)+rangemax(NPO_USD, 0)-rangemax(PR_USD, 0))/PR_USD*100 as DP,

rangemax(PO_USD, 0)+rangemax(NPO_USD, 0)-rangemax(PR_USD, 0) as DV,

I have enclosed the table layout.

By the way, I could only store my question in this place

Paul

Re: How to do cross-table calculations

oknotsen — Thu, 23 Apr 2015 11:13:37 GMT

I do not see why some (if not all) the tables could not be joined together.

Could you describe what the calculation is you are trying to do (without trying to put functions already in there)?

You can do calculations that require values from different tables. Usually be extracting the value and related key into a helper table (mapping table) and using a mapping to temporary get it to the other table (just for the calculation).

Re: How to do cross-table calculations

Thu, 23 Apr 2015 12:05:53 GMT

The three different money values (pramount, poamount and npoamount) can all have different currency units and exchange rates. For that reason I have created three different stand-alone currency exchange tables (MAP_PR_Currency, MAP_PO_Currency and MAP_NPO_Currency) to convert the amounts to USD.

With all the amounts in USD I will be able to do the necessary calculations in order to produce the graphs we want.

If you need more information, please ask.

Re: How to do cross-table calculations

Tue, 13 Oct 2015 06:03:50 GMT

I have found a solution to the problem. It works fine now.

Re: How to do cross-table calculations

oknotsen — Tue, 13 Oct 2015 07:46:10 GMT

Are you willing to share that solution so the topic can be flagged answered?

Re: How to do cross-table calculations

Tue, 13 Oct 2015 09:05:01 GMT

Here is the solution I found to convert currencies amounts to USD amounts where there is the freedom that in one order you can work with different currencies and even with different exchange rates from the same currencies.

In our currency exchange rate table a new currency exchange rate is only written when it has has changed, not standard every month for example.

Step 1: Create all the necessary currency MAP tables

//        Load Currency exchange table

TmpCurrencyExchange:
LOAD [Currency Code] as CurrencyCode,
[Starting Date] as StartingDate,
[Adjustment Exch_ Rate Amount] as AdjExRateAmnt
FROM
[..\..\..\0.SharedFolders\2.QVD\Currency Exchange Rate.qvd]
(qvd);

CurrencyExchange:
load *,
      if(IsNull(StartingDate) or day(StartingDate)='31',Null(),CurrencyCode&'_'&date(StartingDate,'YYYY_MM')) as Curr_Key
resident TmpCurrencyExchange;

//        Create currency mapping tables

Map_PR_Currency:
load Curr_Key as PR_CurrKey,
AdjExRateAmnt as PR_ExRateAmnt
resident CurrencyExchange;

Map_PO_Currency:
load Curr_Key as PO_CurrKey,
AdjExRateAmnt as PO_ExRateAmnt
resident CurrencyExchange;

Map_NPO_Currency:
load Curr_Key as NPO_CurrKey,
AdjExRateAmnt as NPO_ExRateAmnt
resident CurrencyExchange;

Step 2: Join the data tables with the corresponding MAP table

// Load data tables

TmpPR:
LOAD el.id_element_root as DataKey,
prcurr,

if(not IsNull(rangemax(pramount,0)),rangemax(pramount,0),0) as pr_amount,
pick(match(prcurr, 'AED','AFA','AON','AUD','CAD','CDF','CHF','CFH','chf','DKK','ETB','EUR','EURO','Euro','euro',

'GBP','gbp','HTG','IDR','INR','IQD','IRR','JOD','JPY','KES','LBP','LKR', 'MGA', 'MMK', 'MYR',