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.
Solved! Go to Solution.
@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.
@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!
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).
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 Attached workflow joins on Lawyer. Let me know if this works for you!
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.
@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!