topic Re: Calculate Cross Rates for FX Rates in QlikView

Calculate Cross Rates for FX Rates

freddean — Wed, 25 Nov 2020 16:16:04 GMT

Hi All

I have a table of daily USD:[OTHER-CCY] FX rates. e.g. I have USD:ARS, USD:BRL, USD:HUF.

What I want to do is to create a cross table for each of the other currencies per day.

e.g. I need daily to make [ccy1]:[ccy2] - ARS:BRL, ARS:HUF, BRL:ARS, BRL:HUF, HUF:ARS, HUF:BRL

This is done by: taking 1/ccy1(rate) * ccy2(rate)

I want to make 1 table containing all the cross rates for each day, but I want to do it in the data load editor.
Example below:

I know the logic of how to do it, but I don't know how to implement it in Qlik at all.

Thank you for taking the time to read this. I look forward to reading your replies.

Re: Calculate Cross Rates for FX Rates

Stoyan_Terziev — Thu, 28 Nov 2019 12:43:02 GMT

Hi Fred,

The Interval Match function does magic for what you need to achieve. Check it out here:
https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptPrefixes/IntervalMatch.htm

I hope that helps!

Kind regards,

S.T.

Re: Calculate Cross Rates for FX Rates

freddean — Thu, 28 Nov 2019 13:45:32 GMT

Hi Stoyan, I'm not sure if I don't understand the interval match function. But from looking into it this doesn't seem to be what I am looking for at all.

Re: Calculate Cross Rates for FX Rates

Brett_Bleess — Tue, 10 Dec 2019 19:16:50 GMT

Best I have is the following Design Blog post, hopefully it may be what you were looking for to help you out:

https://community.qlik.com/t5/Qlik-Design-Blog/The-Crosstable-Load/ba-p/1468083

Regards,
Brett