topic Re: Currency rates in QlikView

Currency rates

Wed, 06 Apr 2016 10:53:59 GMT

Hi folks!

I have a table in my database that stores currency rates, called FXRates. And the executionreport stores execution per local currency.

What I want to do is convert to a certain currency. This is what the SQl looks like at least.

How would you perform this?

select sum(trade_price * traded_amount * 0.000025 / case when e.currency = 'EUR' then 1.0 else f.rate_eur end) as Turnover, e.currency

from ExecutionReport e

left join FXRate f

on f.currency = e.currency

and f.date = cast(e.[time] as date)

Where client_id = 'blabla'

and exec_broker = 'blablabla'

and user_id = 'blablabla'

and sub_exec_broker is null

and [time] between '2016-03-02' and '2016-03-31'

and market_order_id is not null

and exec_type in ('Fill', 'Partial fill')

group by e.currency

Re: Currency rates

Kushal_Chawda — Wed, 06 Apr 2016 11:04:24 GMT

Creating the connection string from qlikview to connect to database using ODBC/OLEDB drivers.

Run this query directly from qlikview and store it into the QVD

ODBC .....

Currency:

select sum(trade_price * traded_amount * 0.000025 / case when e.currency = 'EUR' then 1.0 else f.rate_eur end) as Turnover, e.currency

from ExecutionReport e

left join FXRate f

on f.currency = e.currency

and f.date = cast(e.[time] as date)

Where client_id = 'blabla'

and exec_broker = 'blablabla'

and user_id = 'blablabla'

and sub_exec_broker is null

and [time] between '2016-03-02' and '2016-03-31'

and market_order_id is not null

and exec_type in ('Fill', 'Partial fill')

group by e.currency;

STORE Currency into Path\Currency.qvd(qvd);

Drop table Currency;

Re: Currency rates

amit_saini — Wed, 06 Apr 2016 11:17:28 GMT

Hi,

This might help u!

Thanks,

Re: Currency rates

jonathandienst — Wed, 06 Apr 2016 11:35:51 GMT

Grouping and filtering in your SQL server is often the better option. To do this in QV, you will be transferring a (much?) larger data set over the network which may impact on performance. Just he SQL query in your load script with the SQL prefix. If you need to pass the [time] filter from QV, define it into a variable which you can add to the SQL script via a $ expansion.

Re: Currency rates

Wed, 06 Apr 2016 11:46:02 GMT

I made a QVD that loads current months rates from the FXRate table. Now How dI perform a join on the executionreport you see the join in my initial sql query.

Re: Currency rates

Wed, 06 Apr 2016 11:58:45 GMT

How would I perform this join in QV:

select *

from ExecutionReport e

left join FXRate f

on f.currency = e.currency

and f.date = cast(e.[time] as date)

Re: Currency rates

Kushal_Chawda — Thu, 07 Apr 2016 10:05:58 GMT

Create the QVD for ExecutionReport and do the bwlow

Currency:

LOAD

date(Date,'DD/MM/YYYY') as Date,

currency ,

...other required columns

FROM ExecutionReport.qvd(qvd)

left join(Currency)

LOAD date(floor([time]),'DD/MM/YYYY') as Date,

currency ,

other required ccolumns

FROM currency.qvd(qvd)