exchange rate calculation on Master calendar table

[grajmca_sgp123]

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

[pokassov]

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];

[grajmca_sgp123]

Thank you for your quick reply let me try

[Anonymous]

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)';

Next

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

[grajmca_sgp123]

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.

[pokassov]

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;

[grajmca_sgp123]

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.

[pokassov]

Hi!

Can you check with your qvd resources?

[grajmca_sgp123]

we are getting field not found error

Field not found

Left Join (Orders)

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

[pokassov · Accepted solution]

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

See attach.