topic exchange rate calculation on Master calendar table in QlikView

exchange rate calculation on Master calendar table

grajmca_sgp123 — Tue, 18 Aug 2015 12:39:06 GMT

Hi All,

I have two tables Orders and Currency table. Orders values are in local currency format, I want to convert this local currency to USD format by using Currency table. Here below are the sample tables

Orders: we have values for every date

OrderDate	Sales	Local Currency
6/15/2014	100	EUR
8/22/2014	120	EUR
9/14/2014	80	EUR
11/11/2014	200	EUR
12/2/2014	150	EUR
1/14/2015	300	EUR
2/28/2015	50	EUR
6/25/2015	70	EUR
8/15/2015	100	EUR

Currency table: we have exchange rate values for every quarter.

From Date	Currency	Exachange rate
6/2/2014	EUR	0.55
9/1/2014	EUR	1.24
12/1/2014	EUR	0.00123
3/1/2015	EUR	0.34
6/1/2015	EUR	0.5

I want to join these tables based on From Date and Order Date fields, but I do not have all FromDate values in Currency table so we need to create master calendar data for From Date. I am fine with Master Calendar table I know how to create it but my requirement is ,if OrderDate fall between FromDate quarter dates i need to pick that date related exchange rate and multiple with sales.

Example:

OrderDate	Sales	Local Currency
6/15/2014	100	EUR
8/22/2014	120	EUR
9/14/2014	80	EUR
11/11/2014	200	EUR
12/2/2014	150	EUR
1/14/2015	300	EUR
2/28/2015	50	EUR
6/25/2015	70	EUR
8/15/2015	100	EUR

for example in above table first order(6/15/2015) fall between 6/2/2014 and 9/31/2014(From date field),So i need to pick exchange rate related to FromDate quarter and calculate sales like Sum(100 * 0.55)

For 3rd order(9/14/2014) fall between 9/1/2014 and 12/1/2014. So sum value is Sum(80*1.24)

same way for other date also.

output looks like this.

OrderDate	Sales
6/15/2014	100*0.55
8/22/2014	120*0.55
9/14/2014	80*1.24
11/11/2014	200*	1.24
12/2/2014	150	*0.00123
1/14/2015	300	* 0.00123
2/28/2015	50	* 0.00123
6/25/2015	70	*.05
8/15/2015	100	*.50

please let me know your inputs here,Thanks in advance

Re: exchange rate calculation on Master calendar table

pokassov — Tue, 18 Aug 2015 12:49:26 GMT

Hello!

1. change your currency table:

NullAsValue [FromDate], [Till Date];

t1:

load

date(previos(From Date)-1) As [Till Date]

resident [Currency table]

order by [From Date] desc;

drop table [Currency table];

2. Use intervalmatch.

left join (Orders)

intervalmatch (OrderDate)

load FromDate], [Till Date]

Resident t1;

left join (Orders)

load FromDate], [Till Date], [Exachange rate]

Resident t1;

drop table t1;

drop fields [FromDate], [Till Date];

Re: exchange rate calculation on Master calendar table

grajmca_sgp123 — Tue, 18 Aug 2015 13:43:50 GMT

Thank you for your quick reply let me try

Re: exchange rate calculation on Master calendar table

Anonymous — Tue, 18 Aug 2015 14:41:15 GMT

HI Rajesh,

Please try:

Exchange_Rates:

LOAD * Inline

[

From Date,Currency,Exchange rate

6/2/2014,EUR,0.55

9/1/2014,EUR,1.24

12/1/2014,EUR,0.00123

3/1/2015,EUR,0.34

6/1/2015,EUR,0.5

];

NoConcatenate

Temp:

Load [From Date],

Currency,

[Exchange rate]

Resident Exchange_Rates ;

DROP Table Exchange_Rates;

MinMaxFromDate:

Load Min([From Date]) as MinDate,

Max([From Date]) as MaxDate resident Temp;

Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;

Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;

Join (Temp)

Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate;

Exchange_Rates:

NoConcatenate Load [From Date],

Currency,

If( IsNull( [Exchange rate] ), Peek( [Exchange rate] ), [Exchange rate] ) as [Exchange rate]

Resident Temp

Order By [From Date] ; /* so that above values can be propagated downwards */

Drop Table MinMaxFromDate, Temp;

Temp:

Load Distinct Currency as Currencies

Resident Exchange_Rates;

For i=0 to FieldValueCount('Currencies')-1

Let vCurrency=Peek('Currencies', $(i), 'Temp');

Exchange_Rate_Map:

Mapping Load [From Date] as OrderDate,

[Exchange rate]

Resident Exchange_Rates

Where Currency = '$(vCurrency)';

Drop Tables Temp,Exchange_Rates;

Sales:

LOAD *,Sales * [Exchange rate] as Amount;

LOAD *,

ApplyMap('Exchange_Rate_Map',OrderDate) as [Exchange rate];

LOAD * Inline

[

OrderDate,Sales,Currency

6/15/2014,100,EUR

8/22/2014,120,EUR

9/14/2014,80,EUR

11/11/2014,200,EUR

12/2/2014,150,EUR

1/14/2015,300,EUR

2/28/2015,50,EUR

6/25/2015,70,EUR

8/15/2015,100,EUR

];

Regards

Neetha

Re: exchange rate calculation on Master calendar table

grajmca_sgp123 — Wed, 19 Aug 2015 06:34:54 GMT

Hi Sergey,

Looks we have close to your solution but joining should be based on OrderDate & CurrencyCode(Concatinate field) instead of OrderDate .

So how can we achieve this.

Re: exchange rate calculation on Master calendar table

pokassov — Wed, 19 Aug 2015 06:50:06 GMT

Hi!

You can add key into intervalmatch

Inner Join IntervalMatch (Date,CurrencyCode) LOAD FirstDate, LastDate, CurrencyCode resident Currency.

And take in mind you should create interval data for each Currency. Something like this:

load

if(previous([Currency Code])=[Currency Code],date(previos(From Date)-1) As [Till Date]

resident [Currency table]

order by

[Currency Code],

[From Date] desc;

Re: exchange rate calculation on Master calendar table

grajmca_sgp123 — Wed, 19 Aug 2015 07:20:53 GMT

Hi Sergey,

i am getting blank in exchange rate field. Kindly find the attached file for script which provided,correct me if i miss any thing.

Re: exchange rate calculation on Master calendar table

pokassov — Wed, 19 Aug 2015 08:51:04 GMT

Hi!

Can you check with your qvd resources?

Re: exchange rate calculation on Master calendar table

grajmca_sgp123 — Wed, 19 Aug 2015 09:22:29 GMT

we are getting field not found error

Field not found

Left Join (Orders)

IntervalMatch (Dateq,Currencyq) LOAD DateStart, DateEnd, Currencyq resident temp

Re: exchange rate calculation on Master calendar table

pokassov — Wed, 19 Aug 2015 09:35:43 GMT

Yep. Your Orders table have to have fields Dateq,Currencyq.

See attach.

Re: exchange rate calculation on Master calendar table

grajmca_sgp123 — Wed, 19 Aug 2015 10:29:27 GMT

excellent Sergey you are Genius Thank you so much for your hard work