topic Applying a field to 2 other fields in QlikView

Applying a field to 2 other fields

Thu, 28 Apr 2016 13:09:03 GMT

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

Re: Applying a field to 2 other fields

sunny_talwar — Thu, 28 Apr 2016 13:13:39 GMT

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)

Re: Applying a field to 2 other fields

Thu, 28 Apr 2016 13:30:27 GMT

Hi Andrew

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