Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have the following table
Join(MarginData)
LOAD if([Local Currency] = 'CNY', 'RMB', [Local Currency]) As Currency,
[Conversion Factor]
FROM
(
Where CalendarDate = Date(Today(),'YYYY-MM-DD');
I'm joining this to a table called MarginData
IN the MrginData table I have 2 fields
1. LocalCurrency
2. DocumentCurrency
Each record in the table will have both local and document currency fields e.g GBP and EUR
The first table contain the exchange rates for USD for all currencies and I need to be able to apply the exchange rate to both fields and for it to select the right currency
e.g
Record 1 - Local currency GBP= 100 Doc currency EUR = 110
USD rate GBP to USD = 1.40
USD rate EUR to USD = 1.35
I need the record to show
Local currency in USD $140 Doc Currency in EUR = 148.50
I think instead of doing a left join, consider doing a mapping load here:
MappingTable:
Mapping
LOAD if([Local Currency] = 'CNY', 'RMB', [Local Currency]) As Currency,
[Conversion Factor]
FROM
(qvd)
Where CalendarDate = Date(Today(),'YYYY-MM-DD');
MarginData:
LOAD AllYourOtherStuff,
ApplyMap('MappingTable', LocalCurrency, Null()) as LocalCurrencyRate,
ApplyMap('MappingTable', DocumentCurrency, Null()) as DocumentCurrencyRate
FROM/Resident .....
UPDATE: I have used a false condition within the ApplyMap as Null(), but you might want to add another value such as 1 (The part in Green Above)
I think instead of doing a left join, consider doing a mapping load here:
MappingTable:
Mapping
LOAD if([Local Currency] = 'CNY', 'RMB', [Local Currency]) As Currency,
[Conversion Factor]
FROM
(qvd)
Where CalendarDate = Date(Today(),'YYYY-MM-DD');
MarginData:
LOAD AllYourOtherStuff,
ApplyMap('MappingTable', LocalCurrency, Null()) as LocalCurrencyRate,
ApplyMap('MappingTable', DocumentCurrency, Null()) as DocumentCurrencyRate
FROM/Resident .....
UPDATE: I have used a false condition within the ApplyMap as Null(), but you might want to add another value such as 1 (The part in Green Above)
Hi Andrew
Can you maybe share an example of the 2 tables with a couple of rows?