Currency rates

[#1]

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

[Kushal_Chawda]

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;

[amit_saini]

Hi,

This might help u!

Thanks,

AS

[jonathandienst]

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.

[#5]

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.

[#6]

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)

[Kushal_Chawda]

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)