Index Match replacement for FX rate based on

[a4nola]

I am trying to achieve a similar result to index match and fill out the FX rate on the second chart using the Date period and the Currency Code.

How would this be achieved in Alteryx? I attempted Join but would need another formula on it not sure if there is a quicker way

Currency Code	202301	202302	202303	202304	202305
AFA	0.011545	0.011121	0.011183	0.011373	0.011555
AFN	0.011545	0.011121	0.011183	0.011373	0.011555
ALL	0.009257	0.009234	0.009249	0.009203	0.009704
DZD	0.007259	0.007324	0.007325	0.007321	0.0074
AOK	0.000001	0.000001	0.000001	0.000001	0.000001
AOA	0.001964	0.001977	0.001961	0.00196	0.001956
AOR	0.000001	0.000001	0.000001	0.000001	0.000001

Currency Code	CCY	FX Rate
202301	AFA
202302	AFN
202311	ALL
202302	DZD
202302	AOK
202306	AOA
202307	AOR
202308	XCD
202309	ARS
202310	AMD
202311	AWG
202312	AUD

[kamal03]

Can you please provide a sample output? 

[a4nola]

Here is the sample Output 

Currency Code	CCY	FX Rate
202301	AFA	0.011545
202302	AFN	0.011121
202311	ALL	0.010035
202302	DZD	0.007324
202302	AOK	0.000001
202306	AOA	0.001949
202307	AOR	0.000001
202308	XCD	0.37037
202309	ARS	0.002857
202310	AMD	0.002594
202311	AWG	0.558659
202312	AUD	0.63768

[aatalai]

can you transpose the data? you might then need to use a select tool to reorder the columns, but thats how I would do it. LEt me know if that helps

[a4nola]

I cut down the output file to make it simpler but in the actual file there would many different fields along with Acct period and Currency. So transposing wont work for this use case.

[aatalai]

@a4nola couldn't you set those fields as key columns in the transpose tool?

[kamal03]

Hi @a4nola 

Please correct me if I am wrong, Based on my understandingI have created this workflow, Let me know if this aligns with your requirements or not.

Thanks

Kamal Sharma