Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Currency lookup based on month of sales_date

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

11 Replies
miguelbraga
Partner - Specialist III
Partner - Specialist III

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
Not applicable
Author

It seems you are talking about Currency Conversion??

see this:  Currency Conversion

Not applicable
Author

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 !

Not applicable
Author

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

Thanks

tresesco
MVP
MVP

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
Master III
Master III

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)

Not applicable
Author

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
Creator II
Creator II

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
    Master III
    Master III

    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.