Currency lookup based on month of sales_date

[#1]

Hi I have the following table :

manager, sales_amount, date, currency_code

Tom, 3000, 7/26/2014, EUR

Bob, 58000, 9/28/2014, GBP

Aaron, 68500, 1/8/2014, CAD

using the lookup provided I want to convert the sales amounts to USD based on the month in the date column of both the lookup and original table.

So effectively the lookup should only do matching based on the currency code and month ( regardless of day ).

Note : The date in the table is in MM/DD/YYY  and in the lookup in DD/MM/YYYY.

Hope that makes sense

[miguelbraga]

Hi Sid,

I think I didn't understood very well your needs, can you please describe your issue with an example? By the way, thanks for the .csv

Regards,

MB

[Anonymous]

It seems you are talking about Currency Conversion??

see this:  Currency Conversion

[#4]

Hi Braga

I basically just want to convert the sales_amount in my existing table all to USD using the lookup provided based on month.

Hope that makes more sense !

[#5]

yes this is currency conversion. Would be really good to get some help around my specific issue.

Thanks

[tresesco]

TableJoin:

Load

          // UniqueName,

          FromCurrency as currency_code,

          ToCurrency,

          Rate,

           Month(date#(Date, 'MM/DD/YYYY')) as Month

From <currency.csv path> where ToCurrency = 'USD';

Right Join

Load

          manager,

          sales_amount,

          Month(date#(date, 'DD/MM/YYYY')) as Month,

           currency_code

From <>;

Final:

Load

     *,

     sales_amount*Rate as USDAmount

Resident TableJoin;

Drop table TableJoin;

Edit: Updated the missed date format differentiation.

[settu_periasamy]

Hi,

Try to convert your date in the existing table using Date#() function. Then it will automatically link.

Data:

Load *,currency_code&'-USD' as UniqueName;

Load manager, sales_amount, Date(Date#(Date,'M/D/YYYY')) as Date, currency_code;

LOAD * INLINE [

    manager, sales_amount, Date, currency_code

    Tom, 3000, 7/26/2014, EUR

    Bob, 58000, 9/28/2014, GBP

    Aaron, 68500, 1/8/2014, CAD

];

Left Join(Data)

LOAD UniqueName,

     FromCurrency,

     ToCurrency,

     Rate,

     Date

FROM

[QV - Others\QVcomm\CURRENCY.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

You can multiply with sales_amount * Rate

Note:

if you want separate the Tables(Currncy Converter and fact table), Need to create the Key field for UniqueName & Date)

[#8]

This looks really good but still not quite working for me.

Slight edit my date format in main table has changed to YYYY-MM-DD however the following statement wont work :

Month(date#(date,'YYYY-MM-DD')) as Month,

[anlonghi2]

Hi Sid,

please try attached qvw.

Two possible approaches are possible, depending on your needs:

• 1st.approach: you need to have the amount in USD in your data model, in this case you have to delete the script between
  1. "//----> use only if you need to change currency to USD on the fly" and,
  2. "//<---- use only if you need to change currency toUSD on the fly"

• 2nd approach: you need to calculate the amount in USD on the fly (eg: in a pivot expression), in this case you have to delete the script between:
  1. "//--> use only if you need to convert to USD in script" and,
  2. "//<-- use only if you need to convert to USD in script"

In the attached qvw both approaches are available.

Let me know

Best regards

Andrea

[settu_periasamy]

Hi sid,

Your two table Date format should be the same (Currency Table and Main Table), And 'Month' Field also should be there in both table.