Need help to replicate logic in alteryx

[AhanaR]

Hi team,

Need Support

Could someone please help replicate this logic in alteryx

If Document currency = "USD" then (Backlog Value in Document Currency), elseif document currency <> "USD" then (Backlog Value in Document Currency */ plan rates). Additionaly I want to add a logic that will multiply document currency with plan rate if the field market convention ="Multiply", else divide if the filed says "Divide"

[AhanaR]

My datasets are as below :

Sales Document Number	Backlog Value in Document Currency	Document Currency
123	697.5	USD
235	1694.4	USD
2367	6300	USD
679	9347	USD
156	2420.35	AFN
250	900.54	DZD
251	1950.6	GBP
974	17735.95	EUR
359	44460	FKP

Dataset 2:

Currency Code	Currency Name	Market Convention	Plan rates
AFN	Afghanistan Afghani	Divide	87.49
DZD	Algerian Dinar	Divide	136.702
AOA	Angolan Kwanza	Divide	502
ARS	Argentine Peso	Divide	173.91
AMD	Armenian Dram	Divide	390.3
AUD	Australian Dollar	Multiply	0.6696
AZN	Azerbaijan Manat	Divide	1.695
BHD	Bahraini Dinar	Divide	0.37697
BDT	Bangladesh Taka	Divide	105.52

[ShankerV]

Hi @AhanaR 

Can you please share the expected output also.

[AhanaR]

Hi Shanker, 

Thanks for your reply.

Here is the updated dataset with expected output in col named "Converted document Currency 

Sales Document Number	Backlog Value in Document Currency	Document Currency	Converted Document Currency
123	697.5	USD	697.50
235	1694.4	USD	1,694.40
2367	6300	USD	6,300.00
679	9347	USD	9,347.00
156	2420.35	AFN	27.66
250	900.54	DZD	6.59
251	1950.6	GBP	2,365.69
974	17735.95	EUR	18,853.31
359	44460	FKP	54,045.58

Updated Dataset 2:

Currency Code	Currency Name	Market Convention	Plan rates
AFN	Afghanistan Afghani	Divide	87.49
DZD	Algerian Dinar	Divide	136.702
AOA	Angolan Kwanza	Divide	502
ARS	Argentine Peso	Divide	173.91
AMD	Armenian Dram	Divide	390.3
AUD	Australian Dollar	Multiply	0.6696
AZN	Azerbaijan Manat	Divide	1.695
BHD	Bahraini Dinar	Divide	0.37697
BDT	Bangladesh Taka	Divide	105.52
GBP	British Pound	Multiply	1.2128
EUR	EURO	Multiply	1.063
FKP	Falkland Islands Pound	Multiply	1.2156

[ShankerV]

Hi @AhanaR 

One way of doing this,

Many thanks

Shanker V

[ShankerV]

Hi @AhanaR 

Step 1: Input Dataset 1

Step 2: Input dataset2

Step 3: Record Id tool

Many thanks

Shanker V

[ShankerV · Accepted solution]

Hi @AhanaR 

Step 4: Join tool

Step 5; 

Step 6: Sort tool

Step 7: 

IF [Document Currency]="USD"
THEN [Backlog Value in Document Currency]
ELSEIF [Market Convention]="Divide"
THEN [Backlog Value in Document Currency]/[Plan rates]
ELSE [Backlog Value in Document Currency]*[Plan rates]
ENDIF

Post run: Output

Many thanks

Shanker V

[Martyn]

You could always use a Find Replace tool with append fields (instead of the Record ID, Join, Union and Sort) to do the Left Join and maintain the record order. The formula tool is still needed for the calculation though.