Discussion Board for collaboration on QlikView Scripting.
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
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
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
Go to Solution.
I think instead of doing a left join, consider doing a mapping load here:
LOAD if([Local Currency] = 'CNY', 'RMB', [Local Currency]) As Currency,[Conversion Factor]FROM(qvd)Where CalendarDate = Date(Today(),'YYYY-MM-DD');
ApplyMap('MappingTable', LocalCurrency, Null()) as LocalCurrencyRate,
ApplyMap('MappingTable', DocumentCurrency, Null()) as DocumentCurrencyRate
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)
Can you maybe share an example of the 2 tables with a couple of rows?