Find & Replace

[Karen_Smith]

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.

[zajaccount · Accepted solution]

Hi

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".

Please find a sample workflow attached.

[grossal]

Hi @Karen_Smith,

I'd suggest to rename your Spot and Average column to B and P. You could than transpose the data and apply them directly using the Join tool.

What do you think?

Best

Alex

[Karen_Smith]

Thank you - that worked.

[Karen_Smith]

I will try your idea also - it's good practice for me to try different ways to solve a problem. Thanks!