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

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

lynnekilgore
7 - Meteor

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.

9 REPLIES 9
NikyN
9 - Comet

Hello @lynnekilgore,

 

can you please provide dummy excels?

 

Best Luck!

Niky

Qiu
20 - Arcturus
20 - Arcturus

@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
Alteryx
Alteryx

@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.

MichelleL_0-1645075974438.png

 

Please let me know if this works for you!

lynnekilgore
7 - Meteor

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

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

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!

MichelleL
Alteryx
Alteryx

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

lynnekilgore
7 - Meteor

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

@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: 

MichelleL_0-1645225822163.png

 

 

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

Labels