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? MonthSystem A Project CodeSystem B Project CodeCodes Match?Employee NameSystem A HoursSystem B HoursHours Difference (A - B)DepartmentDepartment NameApril 2020CODE1CODE1TRUEEmployee 116160DEPT ADEPT AApril 2020CODE2CODE2TRUEEmployee 12438-14DEPT ADEPT AApril 2020CODE 3CODE 3TRUEEmployee 1810-2DEPT ADEPT AApril 2020CODE4CODE4TRUEEmployee 1112118-6DEPT ADEPT AApril 2020CODE5CODE5TRUEEmployee 1440DEPT ADEPT AApril 2020CODE6CODE6TRUEEmployee 1440DEPT ADEPT AApril 2020CODE7CODE7TRUEEmployee 1880DEPT ADEPT AApril 2020CODE8CODE8TRUEEmployee 1440DEPT ADEPT AApril 2020CODE9CODE9TRUEEmployee 146-2DEPT ADEPT AApril 2020CODE10CODE10TRUEEmployee 146-2DEPT ADEPT AApril 2020CODE11CODE11TRUEEmployee 146-2DEPT ADEPT AApril 2020CODE12CODE12NO MATCHEmployee 124024DEPT ADEPT AFebruary 2020CODE13CODE13TRUEEmployee 1440DEPT ADEPT AFebruary 2020CODE14CODE14TRUEEmployee 11127240DEPT ADEPT AFebruary 2020CODE15CODE15TRUEEmployee 146-2DEPT ADEPT AFebruary 2020CODE16CODE16TRUEEmployee 12858-30DEPT ADEPT AFebruary 2020CODE17CODE17TRUEEmployee 11222-10DEPT ADEPT AFebruary 2020CODE18CODE18NO MATCHEmployee 108-8 February 2020CODE19CODE19NO MATCHEmployee 108-8 February 2020CODE20CODE20NO MATCHEmployee 108-8 February 2020CODE21CODE21NO MATCHEmployee 108-8 February 2020CODE22CODE22NO MATCHEmployee 108-8 February 2020CODE23CODE23NO MATCHEmployee 108-8 February 2020CODE24CODE24NO MATCHEmployee 108-8 February 2020CODE25CODE25NO MATCHEmployee 108-8 February 2020CODE26CODE26NO MATCHEmployee 108-8

Match null data in same column based on unique ID

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

Workflow

Datasets

Parse

Join

Preparation

Accepted answers

RobertOdera

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!

RobertOdera

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

For_wboyle2.yxmd

All comments

RobertOdera

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.

RobertOdera

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

For_wboyle2.yxmd

RobertOdera

Thank you @wboyle !

I appreciate your follow up and feedback!

Cheers!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335