Need to change amount according to currency - Qlik Community

rishikeshtiwari · ‎2015-02-01

Hi All,

I have two tables ---

1. In First table I have Currency, Rates Month and Currency Exchange rates.

2 In second table I have Invoice dates ,item Name , Currency,Sales (According to currency in same column) .

I want to add one more column in this table in which sales amount (Column) should convert in EURO Currency(Column)

Actually all the sales amount are coming in that currency which are showing in particular row.

So I want to convert these different types of currency in single currency EUR.

Thanks in Advance

Rishi

robert_mika · ‎2015-02-01

Please see attached

Sales:

LOAD CustomerID,

[Invoice Date],

Currency & right(Date([Invoice Date],'MM-DD-YYYY'),2) & LEFT(Date([Invoice Date],'MM-DD-YYYY'),2)as RateCodePerMonth,

Currency,

[Item Number],

replace(Sales,',','.') as Sales

FROM

[Sales Table.xlsx]

(ooxml, embedded labels, table is Table);

Rates:

LOAD RateCode,

RateCode&RateMonth as RateCodePerMonth,

ExchangeRate

FROM

[Fx Rates.xls]

(biff, embedded labels, table is Sheet1$);

Peter_Cammaert · ‎2015-02-01

Or as an extra column to your Sales data:

MapExchangeRate:
MAPPING
LOAD RateCode & '-' & RateMonth AS F1,
ExchangeRate
FROM [Fx Rates.xls] (biff, embedded labels, table is Sheet1$);

Sales:
LOAD CustomerID,
    [Invoice Date],
    Currency,
    [Item Number],
    Sales,
    Sales * if (Currency = 'EUR', 1, applymap('MapExchangeRate', Currency & '-' & Date([Invoice Date], 'YYMM'), NULL())) AS [Sales EUR]
FROM [Sales Table.xlsx] (ooxml, embedded labels, table is Table)
WHERE (len(trim([CustomerID])) > 0);

What should happen with unmapped currency codes (see IRP in your example?)

Best,

Peter

robert_mika · ‎2015-02-01

As the Sales in Excel file are comma separated you will have to use

replace(Sales,',','.') as Sales

Peter_Cammaert · ‎2015-02-01

I guess that's true if you are in the US.

robert_mika · ‎2015-02-01

You are probably right Peter.

I'm in UK using US Excel version and getting Text values for the Sales column

Peter_Cammaert · ‎2015-02-01

I'm getting Text values too, but the SET statements at the top of my script take care of proper number interpretation.

Peter

robert_mika · ‎2015-02-01

I'm using Personal version at home so I can not see the SET part

Would you mind to post it?

Thank you,

Peter_Cammaert · ‎2015-02-01

I'm using a Personal Edition too, and I can see them. So should you, unless you deleted them first.

Peter_Cammaert · ‎2015-02-01

It seems I made a mistake by assuming that the Exchange Rates were for Foreign Currency -> EUR which is not true.

They appear to convert USD amounts to Foreign Currencies. A double conversion is needed. See script below.

MapExchangeRate:
MAPPING
LOAD RateCode & '-' & RateMonth AS F1,
ExchangeRate
FROM [Fx Rates.xls](biff, embedded labels, table is Sheet1$);

// Correction: XRates seem to imply 'Amount (ForeignCur) / ExchangeRate = Amount (USD)'
// Should convert USD to EUR.

Sales:
LOAD CustomerID,
    [Invoice Date],
    Currency,
    [Item Number],
    Sales,
    Sales * if (Currency = 'EUR', 1,
                applymap('MapExchangeRate', 'EUR-' & Date([Invoice Date], 'YYMM'), NULL()) /
                applymap('MapExchangeRate', Currency & '-' & Date([Invoice Date], 'YYMM'), NULL())
                ) AS [Sales EUR]
FROM [Sales Table.xlsx] (ooxml, embedded labels, table is Table)
WHERE (len(trim([CustomerID])) > 0);

Note that the Sales data may contain amounts formatted as text, but the Exchange Rates do not. Moreover, they display in Excel as #0,00 but have full precision when used in QlikView. Do not use the visible Exchange rates in Excel to verify script output.

Peter