topic Re: Multiple currency conversion at selected date in QlikView

Multiple currency conversion at selected date

rkspareek1992 — Fri, 11 Aug 2017 05:58:51 GMT

Hi,

As per user requirement, in data we have many currencies and wants to change those currencies into different currencies at selected date.

I have attached excel file of my data format and also attached the format in which I have currency conversion format(as in Table).

Kindly suggest how can I change it (at front end).

Re: Multiple currency conversion at selected date

techvarun — Fri, 11 Aug 2017 06:18:24 GMT

You just need to load the file and create a table with

expression Rate* Amt

Dimension From Currency and ToCurrency and Date.

it will work by default.

Thanks,

Varun

Re: Multiple currency conversion at selected date

techvarun — Fri, 11 Aug 2017 06:19:40 GMT

Check if the attached helps.

Let me know if my understanding is wrong.

Re: Multiple currency conversion at selected date

rkspareek1992 — Fri, 11 Aug 2017 06:30:41 GMT

Thanks for suggestion,

But I need rate as of selected date. Means when I select 09-07-2017, then whole from currency to to currency values should be converted at the rate as of 09-07-2017. As per data there is no rate on 09-07-2017, so this should fetch rate as of last updated rate (as of 08-07-2017).

Re: Multiple currency conversion at selected date

effinty2112 — Fri, 11 Aug 2017 08:45:50 GMT

Hi Rakesh,

There are a few steps to take to resolve this.

We need to take your table of exchange rates and make one that defines the period (From and To dates) that the rates are valid for. Then we need to create a calendar that will give every date from first to last (today). The we need to associate each day in the calendar with the exchange rate periods, for this we use the IntervalMatch function. Run this script, don't worry about the synthetic table the IntervalMatch function creates this, it's expected and it not harmful.

Conversions:

Load

Amt,

[From Currency] & '|' & [To Currency] as Currencies;

LOAD [From Currency],

[To Currency],

Amt

FROM

CurrencyConversion.xlsx

(ooxml, embedded labels, table is DataFormat);

[Exchange Rates]:

Load

[From Currency] & '|' & [To Currency] as Currencies,

Date,

Rate;

LOAD [From Currency],

[To Currency],

Date,

Rate

FROM

CurrencyConversion.xlsx

(ooxml, embedded labels, table is RateTableFormat);

[Ordered Rates]:

LOAD

Currencies,

Rate,

Date as From,

If(Currencies = Previous(Currencies),Date(Previous(Date)-1), Today()) as To

Resident [Exchange Rates] Order by Currencies, Date desc;

Drop table [Exchange Rates];

FirstDate:

Load min(Temp) as FirstDate;

LOAD FieldValue('From',IterNo()) as Temp

AutoGenerate 1 While not IsNull(FieldValue('From',IterNo()));

Let vFirstDate = Floor(Peek('FirstDate',0,'FirstDate'));

DROP Table FirstDate;

LET vToday = Floor(Today());

[Calendar]:

LOAD

Ceil((PeriodID+1)/3) as QPeriodID

;

LOAD

AutoNumber(MPeriod, 'MPeriodID') as PeriodID

;

Load

Year(Temp_Date) * 100 + Month(Temp_Date) as MPeriod,

Year(Temp_Date) & 'Q' & Ceil(Month(Temp_Date) / 3) as QPeriod,

Year(Temp_Date) & 'W' & Right('0'&Week(Temp_Date),2) as WPeriod,

Date(Temp_Date-WeekDay(Temp_Date),'DD/MM/YYYY') as WeekBeg,

'Q' & Ceil(Month(Temp_Date) / 3) as Quarter,

Week(Temp_Date) as Week,

Div((Temp_Date-$(vFirstDate)),7) as WeekID,

Month(Temp_Date) as Month,

Month(Temp_Date) & ' ' & Year(Temp_Date) as MthYear,

Date(Temp_Date,'$(DateFormat)') as Date;

Load $(vFirstDate) -1 + IterNo() as Temp_Date

AutoGenerate 1 While $(vFirstDate) -1 + IterNo() <= $(vToday) ;

IntervalMatch:

IntervalMatch(Date) LOAD From, To Resident [Ordered Rates];

Now if you add dates to your table of currency conversions the data model will be able to associate the currencies and the date to the correct conversion date.

Regards

Andrew

Re: Multiple currency conversion at selected date

qliksus — Fri, 11 Aug 2017 11:55:01 GMT

You can create a calendar table using the below script

Amount:
LOAD
[From Currency] &'-'& [To Currency] as Key,
[From Currency],
[To Currency],
Amt
FROM
CurrencyConversion.xlsx
(ooxml, embedded labels, table is DataFormat);

Rate:
LOAD
[From Currency] &'-'& [To Currency] as Key,
[From Currency] as Frm1,
[To Currency] as To1,
Date,
Rate
FROM
CurrencyConversion.xlsx
(ooxml, embedded labels, table is RateTableFormat);

Let vStartDate = '07/01/2017' ;
Let vEnddate = '07/31/2017';

calendar:
load

date(date#('$(vStartDate)')+ IterNo()-1) as Date,
date(date#('$(vStartDate)')+ IterNo()-1) as Date1

AutoGenerate(1)
While date('$(vStartDate)'+ IterNo()-1 ) <=date('$(vEnddate)') ;

In the front end use the below expression

Exp: pick( IsNull([From Currency])+2 , sum({<Date={'$(=max({1<Date={"<$(=max(Date))"}>}Date))'}>}Amt)*sum({<Date={'$(=max({1<Date={"<$(=max(Date))"}>}Date))'}>}Rate),

sum(Amt)*sum(Rate))