Match null data in same column based on unique ID

[wboyle]

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

[RobertOdera · Accepted 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!

[wboyle]

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.

[wboyle]

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

[RobertOdera]

Hi, @wboyle 

[RobertOdera]

Just seeing this now, @wboyle 

Glad you worked it out!

[RobertOdera · Accepted solution]

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

[RobertOdera]

Thank you @wboyle !

I appreciate your follow up and feedback!

Cheers!