Hi Team,
Could you please help here?
How can I achieve this in alteryx ? In excel I use lookup and do the calculation
The logic I need to replicate is as below :
If Document currency = "USD" then (Backlog Value in Document Currency) elseif document currency <> "USD" then (Backlog Value in Document Currency */ plan rates). Additionally, I want to add a logic that will multiply document currency with plan rate if the field market convention ="Multiply", else divide if the field says "Divide"
Field Name | Dataset Name |
Backlog Value in Document Currency | Dataset 1 |
Document Currency | Dataset 1 |
Market Convention | Dataset 2 |
Plan rates | Dataset 2 |
Dataset 1
Sales Document Number | Backlog Value in Document Currency | Document Currency | Converted Backlog Value |
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 |
@AhanaR One way of doing this, I used the currency code to lookup, (if you are using a different field you can change in the find and replace tool)
@AhanaR This my approach using:
Multi-row tool:
Filter Tool
And Message tool
The 2 message tools to pick up when dataset 2 is missing the currency code or when Market convention is not Multiply or Divide.