How to convert currency from different currency to usd

[#1]

Hi,

I have Fromcurrency, Tocurrency , StartDate, Rate. Here I have different currency types and  need to create EndDate and convert all difference currency to USD.

please guide me to finish this conversion.

[johnw · Accepted solution]

For end date, maybe something like:

Conversions2:
LOAD *
,if(Fromcurrency=previous(Fromcurrency)
and Tocurrency=previous(Tocurrency)
,previous(StartDate)-1
,today()+10000) as EndDate
FROM Conversions1
ORDER BY
Fromcurrency
,Tocurrency
,StartDate DESC
;

Then intervalmatch back to your main table using the Fromcurrency, StartDate, and EndDate where the Tocurrency = 'USD'.

[#3]

Hi John,

Thanks for your reply and it's very help full.

I need the explanation about the

Date(if(FromCurrency=previous(FromCurrency)

and ToCuurency=previous(ToCuurency)

,previous(StartDate)-1

,today()+10000))

why currency = previous(currency)

I you have posted earlier can you please direct me to that post.

Thanks.

[#4]

Hi John,

There is Price in Transaction table, I need to multiply Price with Rate column which is from currency table, that Rate value should match with startdate, enddate  with Invoicedate in Fact table.

Could you please guide me how can I map this.

[johnw]

Here's some example data sorted in the order of the load I showed.

Row,Fromcurrency,Tocurrency,StartDate,Rate
1,CAD,Euro,2016-12-01,.8
2,CAD,Euro,2016-11-15,.7
3,CAD,Euro,2016-10-04,.8
4,CAD,USD,2016-12-04,.8
5,CAD,USD,2016-11-17,.7
6,CAD,USD,2016-11-01,.6

To understand what my expression is doing, follow the logic row by row.

Row 1: Is from currency = previous from currency? No, so use today()+10000 as EndDate.

1,CAD,Euro,2016-12-01,.8,2044-05-15

Row 2: Is from currency = previous from currency? Yes. Is to currency = previous to currency? Yes. Use the previous start date (2016-12-01) less 1 day as the end date.

2,CAD,Euro,2016-11-15,.7,2016-11-30

Row 3: Is from currency = previous from currency? Yes. Is to currency = previous to currency? Yes. Use the previous start date (2016-11-15) less 1 day as the end date.

3,CAD,Euro,2016-10-04,.8,2016-11-14

Row 4: Is from currency = previous from currency? Yes. Is to currency = previous to currency. No. Use today()+10000 as EndDate.

4,CAD,USD,2016-12-04,.8,2044-05-15

Row 5: Is from currency = previous from currency? Yes. Is to currency = previous to currency? Yes. Use the previous start date (2016-12-04) less 1 day as the end date.

5,CAD,USD,2016-11-17,.7,2016-12-03

Row 6: Is from currency = previous from currency? Yes. Is to currency = previous to currency? Yes. Use the previous start date (2016-11-17) less 1 day as the end date.

6,CAD,USD,2016-11-01,.6,2016-11-16

And so we see that the load assigns all the right end dates to our currency conversions. We need those end dates to do the intervalmatch.

[johnw]

Now that we have the table, we can use intervalmatch, which I gather you've not used before? I think it would look like this.

LEFT JOIN (Transactions)
INTERVALMATCH (InvoiceDate,Currency)
LOAD
StartDate
,EndDate
,Fromcurrency as Currency
RESIDENT Conversions2
WHERE Tocurrency = 'USD'
;
LEFT JOIN (Transactions)
LOAD
StartDate
,EndDate
,Fromcurrency as Currency
,Rate
RESIDENT Conversions2
WHERE Tocurrency = 'USD'
;
LEFT JOIN (Transactions)
LOAD
TransactionUniqueKey
,Price * Rate as PriceUSD
RESIDENT Transactions
;

Another approach would be to generate a conversion rate for every single day, assuming they change daily or less often, not more often. You could use that as a mapping table and applymap to get the conversion rate and multiply by the price all in one step. That might or might not be faster. If you run into performance problems loading the new table and doing the three joins above, that might be an alternative to consider.

If I get time today, maybe I'll throw together a sample file showing both techniques in action.

[#7]

Hi John,

Thanks for your great time. It's working and Thanks for your explanation.