Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Beginner Data Prep Question...

shong
7 - Meteor

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

8 REPLIES 8
cbridges
11 - Bolide

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.

@thizviz
JohnJPS
15 - Aurora

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
7 - Meteor

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

 

:)

shong
7 - Meteor

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

shong
7 - Meteor

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!

 

 

cbridges
11 - Bolide

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.

@thizviz
shong
7 - Meteor

cbridges,

 

awesome!!! this helped me do exactly what I wanted.

 

Just out of curiostiry, for your workflow, do you need to manage large excel lookup tables for this method and just use Alteryx to merge them?

 

:)

cbridges
11 - Bolide

Yes, some of my lookup tables can be upwards of 19,000 lines with 30 collumns.

@thizviz
Labels