Hello,
I've done this before but can't seem to wrap my brain around it this time and have not had success looking around the forums/google.
A scrubbed snapshot of my data set is below. I am comparing data entered against codes in 2 different systems. System A has the Department and Department Name fields, while System B does not. I am trying to say that when Department and Department Name are null, find the matching Employee Name and fill in the null value based on the record that does have the department info.
I have a larger workflow that gives me this existing output, so maybe it makes sense to queue something up before the data sets are joined instead of doing this after the join?
Month | System A Project Code | System B Project Code | Codes Match? | Employee Name | System A Hours | System B Hours | Hours Difference (A - B) | Department | Department Name |
April 2020 | CODE1 | CODE1 | TRUE | Employee 1 | 16 | 16 | 0 | DEPT A | DEPT A |
April 2020 | CODE2 | CODE2 | TRUE | Employee 1 | 24 | 38 | -14 | DEPT A | DEPT A |
April 2020 | CODE 3 | CODE 3 | TRUE | Employee 1 | 8 | 10 | -2 | DEPT A | DEPT A |
April 2020 | CODE4 | CODE4 | TRUE | Employee 1 | 112 | 118 | -6 | DEPT A | DEPT A |
April 2020 | CODE5 | CODE5 | TRUE | Employee 1 | 4 | 4 | 0 | DEPT A | DEPT A |
April 2020 | CODE6 | CODE6 | TRUE | Employee 1 | 4 | 4 | 0 | DEPT A | DEPT A |
April 2020 | CODE7 | CODE7 | TRUE | Employee 1 | 8 | 8 | 0 | DEPT A | DEPT A |
April 2020 | CODE8 | CODE8 | TRUE | Employee 1 | 4 | 4 | 0 | DEPT A | DEPT A |
April 2020 | CODE9 | CODE9 | TRUE | Employee 1 | 4 | 6 | -2 | DEPT A | DEPT A |
April 2020 | CODE10 | CODE10 | TRUE | Employee 1 | 4 | 6 | -2 | DEPT A | DEPT A |
April 2020 | CODE11 | CODE11 | TRUE | Employee 1 | 4 | 6 | -2 | DEPT A | DEPT A |
April 2020 | CODE12 | CODE12 | NO MATCH | Employee 1 | 24 | 0 | 24 | DEPT A | DEPT A |
February 2020 | CODE13 | CODE13 | TRUE | Employee 1 | 4 | 4 | 0 | DEPT A | DEPT A |
February 2020 | CODE14 | CODE14 | TRUE | Employee 1 | 112 | 72 | 40 | DEPT A | DEPT A |
February 2020 | CODE15 | CODE15 | TRUE | Employee 1 | 4 | 6 | -2 | DEPT A | DEPT A |
February 2020 | CODE16 | CODE16 | TRUE | Employee 1 | 28 | 58 | -30 | DEPT A | DEPT A |
February 2020 | CODE17 | CODE17 | TRUE | Employee 1 | 12 | 22 | -10 | DEPT A | DEPT A |
February 2020 | CODE18 | CODE18 | NO MATCH | Employee 1 | 0 | 8 | -8 | ||
February 2020 | CODE19 | CODE19 | NO MATCH | Employee 1 | 0 | 8 | -8 | ||
February 2020 | CODE20 | CODE20 | NO MATCH | Employee 1 | 0 | 8 | -8 | ||
February 2020 | CODE21 | CODE21 | NO MATCH | Employee 1 | 0 | 8 | -8 | ||
February 2020 | CODE22 | CODE22 | NO MATCH | Employee 1 | 0 | 8 | -8 | ||
February 2020 | CODE23 | CODE23 | NO MATCH | Employee 1 | 0 | 8 | -8 | ||
February 2020 | CODE24 | CODE24 | NO MATCH | Employee 1 | 0 | 8 | -8 | ||
February 2020 | CODE25 | CODE25 | NO MATCH | Employee 1 | 0 | 8 | -8 | ||
February 2020 | CODE26 | CODE26 | NO MATCH | Employee 1 | 0 | 8 | -8 |
Solved! Go to Solution.
Hi, @wboyle
I feel that your sample file doesn't subscribe to the use case you've described 🙂
However, from my best understanding:
1. Somewhere in your flow (assuming an employee can only be in one Dept, Dept Name), generate a list of unique employee ID, employee Name, Dept, Dept Name
2. Later in your flow, use the Find and Replace Tool to update the null with the appropriate Dept, Dept Name
Cheers!
Thanks @RobertOdera for the response.
In regards to my use case with the sample table, this is all for 1 employee. The bottom 10 rows have nulls for Department and Department Name. I'm trying to Find where the 2 Dept fields are null and replace with the 2 Dept fields where Employee Name matches. I'm playing around with the Find and Replace tool now, but so far don't see how it will work yet.
@RobertOdera - Find and Replace did work! I just didn't know how to use it. My co-worker suggested putting a true false filter in front of it and that got me going. Thanks again!
Just seeing this now, @wboyle
Glad you worked it out!
Hi, @wboyle
Alternative, leaner, approach given your specific use case.
Please also mark as an acceptable solution and like, if this works for you!
Multi-Row Tool config to update Dept
*The group by selection is just in case you ever have more than one unique employee