Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Applying a field to 2 other fields

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

1 Solution

Accepted Solutions

Re: Applying a field to 2 other fields

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)

2 Replies

Re: Applying a field to 2 other fields

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)

Not applicable

Re: Applying a field to 2 other fields

Hi Andrew

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

Community Browser