How to do cross-table calculations

[#1]

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

[oknotsen]

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).

[#3]

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.

[#4]

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

[oknotsen]

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

[#6 · Accepted solution]

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',

    'MZN','NAD','NOK','NZD','PHP','PKR','SDD','SDG','SEK','SGD','SLL','SLS', 'SOS', 'SSP', 'SYP',

    'THB','UGX','USD','usd','XAF','ZAR','ZMK','ZWD'),

    'AED','AFA','AON','AUD','CAD','CDF','CHF','CHF','CHF','DKK','ETB','EUR', 'EUR', 'EUR', 'EUR',

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

    'MZN','NAD','NOK','NZD','PHP','PKR','SDD','SDG','SEK','SGD','SLL','SLS', 'SOS', 'SSP', 'SYP',

                    'THB','UGX','USD','USD','XAF','ZAR','ZMK','ZWD' ) as PR_Currency,

FROM [..\..\..\0.SharedFolders\2.QVD\Wigii Portfolio\Logistics.Procurement.qvd](qvd);

TmpPO:
LOAD el.id_element_root as DataKey,
pocurr,
pick(match(pocurr, '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',

    'MZN','NAD','NOK','NZD','PHP','PKR','SDD','SDG','SEK','SGD','SLL','SLS', 'SOS', 'SSP', 'SYP',

    'THB','UGX','USD','usd','XAF','ZAR','ZMK','ZWD'),

    'AED','AFA','AON','AUD','CAD','CDF','CHF','CHF','CHF','DKK','ETB','EUR', 'EUR', 'EUR', 'EUR',

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

    'MZN','NAD','NOK','NZD','PHP','PKR','SDD','SDG','SEK','SGD','SLL','SLS', 'SOS', 'SSP', 'SYP',

                          'THB','UGX','USD','USD','XAF','ZAR','ZMK','ZWD') as PO_Currency,

FROM [..\..\..\0.SharedFolders\2.QVD\Wigii Portfolio\Logistics.SubProcurmentPO.qvd](qvd);

TmpNoPO:
LOAD el.id_element_root as DataKey,
npocurr,
pick(match(npocurr, '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',

    'MZN','NAD','NOK','NZD','PHP','PKR','SDD','SDG','SEK','SGD','SLL','SLS', 'SOS', 'SSP', 'SYP',

    'THB','UGX','USD','usd','XAF','ZAR','ZMK','ZWD'),

    'AED','AFA','AON','AUD','CAD','CDF','CHF','CHF','CHF','DKK','ETB','EUR', 'EUR', 'EUR', 'EUR',

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

    'MZN','NAD','NOK','NZD','PHP','PKR','SDD','SDG','SEK','SGD','SLL','SLS', 'SOS', 'SSP', 'SYP',

                          'THB','UGX','USD','USD','XAF','ZAR','ZMK','ZWD') as NPO_Currency,

FROM [..\..\..\0.SharedFolders\2.QVD\Wigii Portfolio\Logistics.SubProcurmentNoPO.qvd](qvd);

//        Join data tables with corresponding currency MAP

SubPR:
LOAD *,    if(not IsNull(pr_date),PR_Currency&'_'&date(pr_date,'YYYY_MM'),Null()) as PR_CurrKey

resident TmpPR;

left join  load * resident Map_PR_Currency;

SubPO:
LOAD *,  if(not IsNull(po_date),PO_Currency&'_'&date(po_date,'YYYY_MM'),Null()) as PO_CurrKey
resident TmpPO;

left join load * resident Map_PO_Currency;

SubNoPO:
LOAD *,  if(not IsNull(npo_date),NPO_Currency&'_'&date(npo_date,'YYYY_MM'),Null()) as NPO_CurrKey
resident TmpNoPO;

left join load * resident Map_NPO_Currency;

//        Combine Procurement tables to only one table

TmpSub:
load *,
if(PO_Currency='USD',po_amount,po_amount /PO_ExRateAmnt)  as POUSD
resident SubPO;

Concatenate

load *,
if(NPO_Currency='USD',npo_amount,npo_amount/NPO_ExRateAmnt) as NPOUSD
resident SubNoPO;


Procurement_table:       
load *,   
if(PR_Currency='USD',pr_amount,pr_amount/PR_ExRateAmnt ) as PRUSD
resident SubPR;

join    load * resident TmpSub;