Conditional Lookups using a reference table

[Alex_H]

Id like to ask the Alteryx community for some ideas please to help replicate some functionality that exists in another app I have, but in Alteryx.

In an app I have a table, where data is passed through row by row to establish outcomes.   Within the table, if the data meets certain criteria, then a logical IF statement is run at the end, to provide the output, also, there are some simpler lookups, like if the input value is A,  then output B, etc.

I can't seem to find a lookup function within Alteryx using a reference table.  Want I want to avoid is using the 'formula' tool, and just having many rows of IF statements to do what I need.  Can anyone help with simple ideas to approach this sort of scenario?

[caltang]

You'll need the Join tool. It serves like a VLOOKUP for your scenario.

[caltang]

This link will help: https://knowledge.alteryx.com/index/s/article/Alteryx-for-Excel-Users-How-to-do-a-VLOOKUP-in-Alteryx.... 

[Hammad_Rashid · Accepted solution]

In Alteryx Designer, you can achieve the functionality you described using various tools and workflows. While Alteryx might not have a direct lookup function like some other tools, you can use the Join tool or the Multi-Field Formula tool to accomplish similar tasks. Here are a few ideas:

1. Join Tool for Lookup:

• Use the Join tool to join your input data with a reference table based on a common key.
• Configure the Join tool to perform a Left Join, so that all records from your input data are included.
• After the join, use a Select tool to choose the fields you need from both tables.
• You can now perform conditional logic based on the fields from the reference table.

2. Multi-Field Formula Tool:

• Use the Multi-Field Formula tool to create new fields based on your conditions.
• For example, if your condition is "if the input value is A, then output B," you can create a new field with the formula: IF [InputField] = 'A' THEN 'B' ELSE [OtherField] ENDIF.
• Repeat this process for each condition.

3. Filter Tool for Conditional Logic:

• Use the Filter tool to filter rows based on your criteria.
• For example, if you have a condition "if the data meets certain criteria," you can use the Filter tool to include only the rows that meet those criteria.
• After filtering, apply your logical IF statement using the Multi-Field Formula or other tools.

4. Filter and Formula Combination:

• Combine the Filter and Formula tools to achieve complex logic.
• First, use the Filter tool to filter rows based on certain criteria.
• Then, use the Formula tool to apply logic to the filtered data.

5. Use the Formula Tool for Simple Conditions:

• For simple lookups like "if the input value is A, then output B," use the Formula tool directly.
• Create a new field with a formula like IF [InputField] = 'A' THEN 'B' ELSE [OtherField] ENDIF.

[Alex_H]

thanks for sharing..   i think i need a combination of filters and joins.  thanks for your help