Applying a field to 2 other fields

[#1]

Hi

I have the following table

Join(MarginData)
LOAD if([Local Currency] = 'CNY', 'RMB', [Local Currency]) As Currency,
[Conversion Factor]
FROM

(qvd)
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

[sunny_talwar · Accepted solution]

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)

[#3]

Hi Andrew

Can you maybe share an example of the 2 tables with a couple of rows?