Alteryx designer Discussions

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

Beginner Data Prep Question...

Highlighted
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

Highlighted
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
Highlighted
ACE Emeritus
ACE Emeritus

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!

Highlighted
Meteor

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

 

:)

Highlighted
Meteor

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

Highlighted
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!

 

 

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
Highlighted
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?

 

:)

Highlighted
Bolide

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

@thizviz
Labels