Beginner Data Prep Question...

Hello guys,

I am a new user exploring Alteryx and was wondering if the following scenario is possible as I could not find a way to google my situation.

I extract excel data from 5 different data source systems. Once extracted, I need to clean up and standardize the data (i.e. brand A = product category 1, etc.) and then combine all 5 sources into one master excel file and then load it into Tableau.

Within each of these files, I need to create lookup tables to standardize data across the 5 sources.

Examples

1. Product A = "Product Category 1"

2. Error Code 5 = "Confirmed"

3. United States = "North America"

4. USA = United States

5. Unitd Stats of America = United States (typo examples)

Is there a way to build in this VLOOKUP logic into Alteryx that way every time I extract data, the lookups for specific fields will be stored in Alteryx, so I can simply import updated files into Alteryx and not have to keep re-cleaning my files each time?

For example, I want to have excel equivalent lookup tables built into Alteryx, so if there are new entries that have not been documented before, I can just update and add new new lookup entries to the respective tables.

Thanks,

Sam

Transformation

Preparation

Accepted answers

cbridges

Take a look at the attached. I created some fake data and lookup tables and demonstrated how the left browse will show you the records that are missing in the lookup tables. You probably will have to have multiple lookup tables (so do I) unless you have a common key (like product number) that is in all of your tables.

Also, I demonstrated how you can join fields even if they don't have the same name (product num and product for instance).

Let me know if you have any other questions once you've had a look at the workflow.

JoinExample.yxzp

All comments

cbridges

I need to do this frequently. The way I do it is to bring my lookup table into the workflow as a second input.

Then you can join your data to your lookup table using a join tool.

Add a browse tool to the left output - this is where you will find the items that didn't have a match in your master table.

You can then copy those items from the browse tool into Excel and clean it up however you need to.

Let me know if this is unclear and then I will attach an example.

JohnJPS

The Find/Replace tool would help you create a new column in each table for Joining them all together; then @cbridges already mentioned how to handle the problem cases.

Hope that helps!

shong

Thanks cbridges! Can you please attach an example so I have a reference handy when I explore this method?

shong

Thanks John! I'll take this into consideration and read up on how I could utilize Find & Replace tool.

shong

Hello again cbridges,

I think I got it working....but the problem is that I have multiple lookup tables for each column that requires a lookup.

For example,

I have individual tables for location, product category, brand (to fix typos, etc.).

However, I am not quite sure what the best way it is to join each of these tables within Alteryx so I can export this into a single table with all the updated lookup columns.

Would be great to see your example!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703