topic Re: Question about rates in QlikView

Question about rates

Wed, 25 Nov 2020 16:16:04 GMT

Hey guys,

In my database, I have a a rate table which loads new values in the currencyrate everyday. Thing is it is mapped to EUR (is set to 1)

So to get everything into one currency my exression looks like: Sum(turnover/rate_eur)

If I would like the turnover in say CHF is it a easy way of doing that in the expression somehow?

In excel file:

Thanks!

Re: Question about rates

sunny_talwar — Thu, 21 Apr 2016 10:53:29 GMT

Can you provide the above data in an excel file so that we can create a sample for you?

Re: Question about rates

Thu, 21 Apr 2016 10:55:54 GMT

I have a monthly QVD with data? for rates... what is it that you need?

Thanks

Re: Question about rates

sunny_talwar — Thu, 21 Apr 2016 10:56:47 GMT

The data that you are showing above in a excel file (instead of pasting it as an image).

Re: Question about rates

avinashelite — Thu, 21 Apr 2016 11:01:11 GMT

You need to show the Turnover column in respective rate currency value??

if that is the case , you could create a inline list box to select the respective currency values like USD,EUR etc based on the you write your expression to pick the conversion rate values

Re: Question about rates

Thu, 21 Apr 2016 11:01:32 GMT

Ok, see top Thanks

Re: Question about rates

Thu, 21 Apr 2016 11:03:01 GMT

Ok, yes At least in EUR and SEK not turnover in every separate currency. Yeah ok, how would that be done? sorry im new to this..

Re: Question about rates

sunny_talwar — Thu, 21 Apr 2016 11:03:48 GMT

Check this out:

CurrencyTable:

LOAD * Inline [

Currency, rate_eur

GBP, 0.78805

EUR, 1

CHF, 1.09783

];

CurrencyMapping:

Mapping

LOAD *

Resident CurrencyTable;

Table:

LOAD *,

Turnover / ApplyMap('CurrencyMapping', Local_Currency) as Turnover_eur

Inline [

Turnover, Local_Currency

50, CHF

60, GBP

20, EUR

];

Data Model: Showing two island tables

Re: Question about rates

Thu, 21 Apr 2016 11:13:59 GMT

This looks liek ti but this will give me a static currency rate. I need it more dynamic since the currencies updates everyday.

Re: Question about rates

sunny_talwar — Thu, 21 Apr 2016 11:16:15 GMT

This was just an example, you would be loading the currency table from a source which updates daily and this will make sure that you have most recent data in your application

CurrencyTable:

LOAD *

FROM CurrencyTableSource;

CurrencyMapping:

Mapping

LOAD Currency,

Rate

Resident CurrencyTable;

Table:

LOAD *,

Turnover / ApplyMap('CurrencyMapping', Local_Currency) as Turnover_eur

Inline [

Turnover, Local_Currency

50, CHF

60, GBP

20, EUR

];

Re: Question about rates

Thu, 21 Apr 2016 11:21:34 GMT

Thanks, but this is the rates hardcoded. I get an improt of rates everyday It has to be more dynamic. I mean to get the values in separate currency I would need to update the ratefactor so to speak.

Re: Question about rates

sunny_talwar — Thu, 21 Apr 2016 11:23:06 GMT

I understand that, the above script was just an example. You would be loading the data from your source. I just wanted to show your the data structure and functionality. Continue to load the data from your existing source. Does it make sense?

Re: Question about rates

Thu, 21 Apr 2016 11:25:32 GMT

Yeah ok I think you where into something right I understand your thought but how would this be made to make it to SEK for example 😕

Re: Question about rates

Thu, 21 Apr 2016 11:39:31 GMT

Yes exactly like above with inline. But that example would force me to update the factor its multiplied with. Since I load the rates already I would want a smoother solution.. hmm:S

Re: Question about rates

sunny_talwar — Thu, 21 Apr 2016 11:45:37 GMT

Here I am pulling the data from the Excel that was attached

CurrencyTable:

LOAD Date,

currency,

rate_eur

FROM

rates.xls

(biff, embedded labels, table is Sheet1$);

Right Join(CurrencyTable)

LOAD Max(Date) as Date

Resident CurrencyTable;

CurrencyMapping:

Mapping

LOAD currency,

rate_eur

Resident CurrencyTable;

Table:

LOAD *,

Turnover / ApplyMap('CurrencyMapping', Local_Currency) as Turnover_eur

Inline [

Turnover, Local_Currency

50, CHF

60, GBP

20, EUR

40, SEK

];

Re: Question about rates

Thu, 21 Apr 2016 12:23:52 GMT

Since I have all the values compared to EUR isnt it any smart way in an expression to just

SUM(turnover/rate_eur) * currency='SEK'

Re: Question about rates

sunny_talwar — Thu, 21 Apr 2016 12:31:54 GMT

I have no idea how your data is structured my friend. May be there is an easier way, but until I can see what you have, its difficult to give any suggestions. What I presented was based on whatever information I had

Re: Question about rates

Thu, 21 Apr 2016 12:45:16 GMT

Pardon me Sunny, Im kinda new to this Qlik world maybe I don't provide good enough information. Im sorry. But if you look at the Qvw above maybe it gives u betetr idea?