translation for Quarterly and Non-Quarterly months

[duckduck2001]

Hi Team,

I have two input files: one is the transactions file, and the other contains the monthly exchange rates to USD for each company.

I aim to create a flow such that, when a user opt to generate translation for a quarterly month (Mar, Jun, Sep, Dec), all amounts are translated using quarterly exchange rate (Q1-24,Q2-24...). 

Examples:

Quarterly Month Input:

If the user selects June, amounts for Jan-Mar will be translated using Q1's rate, and amounts for Apr-Jun will be translated using Q2's rate.

Non-Quarterly Month Input:

If the user selects May, amounts for Jan-Mar will be translated using March's rate, amounts for Apr will be translated using April's rate, and amounts for May will be translated using May's rate.

Exchange rate file headers:

Jan-24, Feb-24, Q1-24, Apr-24, May-24, Q2-24......

How should I build this? 

Appreciate your help.

[OTrieger]

@duckduck2001 
There are several things that you will need to do.
1. In User Interface set a button that will indicate if it is a Quarterly or no-quarterly report.
2. You will connect this button to a Detour Tool, where your WF will flow to the right or left based on the user selection
3. For non-quarterly that will be simple as you will need to match between the month and the month exchange rate and get the value calculate
4. for the Quarter you should create flags that indicate that Jan-Mar is Q1 etc, so you can match the Q exchange rage with the individual months, then match them and calculate the value.
5. Add Detour End tool and set the output

Another way to do it will be with batch macro, I think that the above solution is simpler.

[duckduck2001]

Thanks for the suggestion, an easier way i though of is just simply lookup the accounting period since I already merged all the months exchange rate into the transaction row, but i am not sure how to get the below formula to work.

[Transaction] * [Accounting Period] 

Example dataset:
Transaction, Accounting Period, Jan-24, Feb-24
2000, Jan-24, 3.5868, 3.8771

I want the result to be 2000*3.5868 .

[OTrieger · Accepted solution]

@duckduck2001 
Use Data to Column, delimiter comma and you will have each of the items in a separate column, then you will need to change the data type of the value and exchange rate or use in a Formula to ToNumber() function so Alteryx will know that these are 2 numeric values and not regard them as string.