Find & Replace

Hi,

New Alteryx user here.

My main dataset is a trial balance with columns for "Type" and "Currency". Type is "B" for balance sheet accounts and "P" for income statement accounts. Currency is the local currency for the subsidiary (i.e. EUR, USD, CAD etc).

I have a lookup table of foreign exchange rates for each currency with a column for the Spot rate and a column for the Average rate.

I would like to append ONE column to the main dataset for the exchange rate: If the Type is "B", use the spot rate, if the Type is "P", use the average rate. I'm not sure of the most efficient way to do this.

Type	Ac	AcctName	Currency
B	15115	Italy Tax Receivable	USD
B	15197	LIF Currency Suspense	USD
B	17601	Goodwill Post Dec 2008	USD

Currency	Spot	Average
BRL	0.254952	0.275355
CAD	0.735051	0.77158
CHF	1.010611	1.02444

Thank you in advance for suggestions and assistance.

Interface Tools

Accepted answers

zajaccount

not sure if its the most efficient way, but you could filter by Type and then configure the find replace separately for each leg. After this, you union the two legs manually and then change the column name to "exchange".