Alteryx Beginner: Populating Empty Fields Using an Excel Lookup/Reference Table

Hello everyone,

I've tried to find this answer already as I'm certain it exists somewhere in the community, but I just can't find it. I need help populating empty cells. So, I have used the Union tool to join three excel files, and that worked well. However, I noticed that some records are complete, while others are not. Here's an example:

Complete:

Employee ID Name Office Department

00001 Attorney X Sioux Falls Corporate

Incomplete:

Employee ID Name Office Department

Attorney X Corporate

My HR analyst gave me a master employee file that has all of the data I need to populate the empty fields, but there are thousands of records, so I need to figure out how to use this master file in my workflow before moving on to any analysis. Is there anyone who can guide me on how to do this? I hope this is clear. Thank you in advance for your help.

Datasets

Data Investigation

Accepted answers

MichelleL

@lynnekilgore Attached workflow joins on Lawyer. Let me know if this works for you!

JoinOnLawyer.yxzp

MichelleL

@lynnekilgore I'm glad this worked for you. To help with whitespaces, check out the Data Cleansing tool. This may speed up some of the cleaning you need to do: https://help.alteryx.com/20214/designer/data-cleansing-tool . Another fun tool is Fuzzy Match . . . you can set the match threshold to get more matches (i.e. Jon Smith = Jonathan Smith): https://help.alteryx.com/20214/designer/fuzzy-match-tool

Regex may also help speed up data cleansing depending on how tricky it gets: https://help.alteryx.com/20214/designer/regex-tool . And last but not least, regex functions in the formula tool can help a lot with data cleaning:

If my solution above is working for you, please mark it as 'solved'. Thanks & happy Friday!

All comments

NikyN

Hello @lynnekilgore,

can you please provide dummy excels?

Best Luck!

Niky

Qiu

@lynnekilgore
The key here is that you must have some key column data that is unique for all the employees and and it must not be empty.

Otherwise, we can really do anything.

There could be another "Attorney X" in other office or having a different employee ID.

MichelleL

@lynnekilgore I believe you'll need to use a Join tool. A Join tool is the closest thing to an Excel Vlookup. The question is: which field will you use to join? The Name is a possibility, but you run the risk of 2 people with the same name. You can find a example right in Alteryx Designer using the menu bar.

Please let me know if this works for you!

lynnekilgore

Hi Qiu,

Completely understand - and that's unfortunately one of the issues I'm dealing with. No Attorney ID, which could help address all of the other blank columns. I hate to think I'd need to use Excel for a VLOOKUP or INDEX (which defeats the purpose of having this amazing tool).

lynnekilgore

Michelle, thank you for this! I'm not sure I actually have a column - no unique ID of any sort is in my export, just in the reference table. I will give this a look, though, and see what I might figure out. I'll revert here with success/challenges!

lynnekilgore

Here you go - I included all fields that are in the file, with sample dummy records (complete and incomplete). I am hoping to use the HR data to populate the missing values in the dummy data file.

Thank you!

Alteryx dummy data.xlsx

Alteryx HR.xlsx

lynnekilgore

Michelle, this is REALLY fantastic - I'm using it now! My only challenge at this point is some attorneys still aren't coming through - the HR file I have and the dummy data file have lawyer names entered differently in some places (not sure why)...so I'm starting to find one-offs here and there that weren't cleaned and populated by your amazing workflow. I'll keep digging, though (trying the IF THEN ELSE but I'm getting mixed results - kind of like there's whitespace I'm not seeing that's getting in the way of processing certain records). Thank you so much.

MichelleL

If my solution above is working for you, please mark it as 'solved'. Thanks & happy Friday!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703