My currency convector does not work

[Elkhan]

I have one QVD file with currencies which I am attaching with applymap to my FACT table. 

In the data I have 2 currency codes USD when filed in the document is empty and CAD when filed has CAD currency code. 

I am trying to get currency convertor works, however, now it converting not only CAD but also USD. Could you please check script below and advise what might be wrong? 

[Currency_Table_INV]:
Mapping LOAD


Date("Date",'D.M.YYYY') &'-'& currencyCode AS CurrencyKey,

"Rate"

FROM ;

[INV_Lines]:

LOAD*,

IF(currencyCodeINV = '','USD', currencyCodeINV) AS currencyCode,

;

LOAD

(amount) AS amount_LCR,

(amount) * ApplyMap('Currency_Table_INV',Date(postingDate)&'-'&'CAD',1) AS [amount_USD],

amountIncludingVAT,
description,
documentNo,
orderNo,
quantity,
"type",
unitCost,
unitOfMeasure,
unitPrice,
billToCustomerNo,
lineDiscount,
lineDiscountAmount,
locationCode,
"no" AS ProductKey,

Date(postingDate,'D.M.YYYY') AS %Key.Calendar,
quantityBase,
sellToCustomerNo,
unitCostLCY,

ApplyMap('INV_BillToName',documentNo, NULL()) AS [Bill_To_Customer_Name],
ApplyMap('INV_sellToCustomerName',documentNo, NULL()) AS [Sell_To_Customer_Name],
ApplyMap('INV_Currency_Code',documentNo, NULL()) AS [currencyCodeINV],

ApplyMap('INV_Customer_City',sellToCustomerNo) AS [Sell_To_City],
ApplyMap('INV_Customer_State',sellToCustomerNo) AS [State_Code],

ApplyMap('Cost_Center_Sub_Channel_US',COSTCENTER) AS Sub_Channel,
ApplyMap('Cost_Center_Channel_US',COSTCENTER) AS Channel,


ApplyMap('Store_Cost_Center_INV',COSTCENTER) AS [Store],

'Invoice' AS [Document_Type],
'North America' AS [Region],

trim(documentNo &'-'& "no") AS Product_Cost_Key,

vat,
vatBaseAmount,
vatBusPostingGroup,
trmSalesperson,
trmOrderType,
trmOriginalOrderNo,
trmOriginalQuantity,
trmOriginalQuantityBase,
BRAND,
CATEGORY,
"COLLECTION",
COSTCENTER,
COUNTRY,
CUSTOMER,
SEASON
FROM ;

[lennart_mo]

Hi @Elkhan,

By using (amount) * ApplyMap('Currency_Table_INV',Date(postingDate)&'-'&'CAD',1) AS [amount_USD] you imply, that every amount is in CAD. Do you have any field that could serve as a qualifier for wether the currency is CAD or USD?

[Elkhan]

Hi, thank you for your comment, yes I tried to use the country code with IF function. 

amount * IF(COUNTRY='CA',ApplyMap('Currency_Table_INV',Date(postingDate)&'-'&'CAD',1)) AS [amount_USD],

[lennart_mo · Accepted solution]

What results did you get from this? I would assume Amounts that should have been USD were 0s, is that correct? If so, i would suggest either adding a 1 as the ELSE-case in your IF() or only applying the IF() to the 'CAD', so that you alter the currency code.

amount * IF(COUNTRY='CA',ApplyMap('Currency_Table_INV',Date(postingDate)&'-'&'CAD',1),1) AS [amount_USD]

or

amount * ApplyMap('Currency_Table_INV',Date(postingDate)&'-'&IF(COUNTRY='CA','CAD','USD'),1) AS [amount_USD]

[Elkhan]

Thank you! It works!