Identify Part Number to Add in a New Column based on a value in another column

[ssripat3]

Hello community,

I need some advice and/or help regarding some data I am trying to parse and add that data to a new column when some condition matches.

Input Data

Record ID	Prefix	Base	Suffix	External Part Action	Action	Part Number	External Part Number
AA12345	A1CRT	20A001	EA	U	Replace Part	A1CRT-20A001-EA	1AB122135E
AA12345	A1CRT	20A001	EB	N	Add Part	A1CRT-20A001-EB	1AB122135F
BA98765	45ART	1020	CA	U	Replace Part	45ART-1020-CA	3323330226
BA98765	45ART	1020	CA	F	Add Part	45ART-1020-CA	3323330226
BA98765	45ART	1020	CA	U	Replace Part	45ART-1020-CA	3323330226
BA98765	45ART	1020	CA	U	Replace Part	45ART-1020-CA	3323330226
CA45678	96MRT	ANC123	BA	U	Replace Part	96MRT-ANC123-BA	9AB445567H
CA45678	96MRT	ANC123	BBW	F	Add Part	96MRT-ANC123-BBW	9AB445567I

Output Data

Record ID	Prefix	Base	Suffix	External Part Action	Action	Part Number	External Part Number	Replaces Part Number
AA12345	A1CRT	20A001	EA	U	Replace Part	A1CRT-20A001-EA	1AB122135E
AA12345	A1CRT	20A001	EB	N	Add Part	A1CRT-20A001-EB	1AB122135F	A1CRT-20A001-EA
BA98765	45ART	1020	CA	U	Replace Part	45ART-1020-CA	3323330226
BA98765	45ART	1020	CA	F	Add Part	45ART-1020-CA	3323330226	45ART-1020-CA
BA98765	45ART	1020	CA	U	Replace Part	45ART-1020-CA	3323330226
BA98765	45ART	1020	CA	U	Replace Part	45ART-1020-CA	3323330226
CA45678	96MRT	ANC123	BA	U	Replace Part	96MRT-ANC123-BA	9AB445567H
CA45678	96MRT	ANC123	BBW	F	Add Part	96MRT-ANC123-BBW	9AB445567I	96MRT-ANC123-BA

The data from the Input table is being parsed from XML files and Record ID is the value present in the File Name and also a unique identifier in the XML.

Now, let us consider Record ID - AA12345 and I need to build a logic where we are able to create a new column called "Replaces Part Number" based on the value in "External Action" column.

If Record ID between two records are same and External Action is different. In this case, if External Action is "U" for the first record and External Action is "N" for the second record and External Part Number for action "N" is a sequence of the part number in comparison to the part number with action "U".

Another scenario is where the Add Part action will be "F" instead of "N" and the External Part numbers for both of them might be same or different like the above scenario. Examples of these are present in the above table, the scenario with the same Part Number is the Record ID - BA98765 and different part number is with Record ID - CA45678

Can someone help me with solving this logic and the workflow attached has the Expected Input and Output data?

[DavidSkaife · Accepted solution]

Hi @ssripat3 

This appears to be fairly simple, unless i'm missing something:

Using a Multi-row Formula tool configured like the below, noting the Group By on Record ID option

Workflow attached

[ssripat3]

Thank you for the solution @DavidSkaife , it works as expected. But let us say I have some parts where the Action is F for a couple of records and I don't need to traverse the part number to the below row. How can I cover this scenario?

CA45678	86MRT	2000	PA	F	Add Part	86MRT-2000-PA	7RR971806A	86MRT-2000-YA
CA45678	86MRT	2000	PA	F	Add Part	86MRT-2000-PA	7RR971806A	86MRT-2000-PA
CA45678	86MRT	2000	PB	F	Add Part	86MRT-2000-PB	7RR971806B	86MRT-2000-PA
CA45678	86MRT	2000	PB	F	Add Part	86MRT-2000-PB	7RR971806B	86MRT-2000-PB
CA45678	86MRT	2000	PB	F	Add Part	86MRT-2000-PB	7RR971806B	86MRT-2000-PB
CA45678	86MRT	RCTD321	HA	F	Add Part	86MRT-RCTD321-HA	7RR971896C	86MRT-2000-PB
CA45678	86MRT	RCTD321	HA	F	Add Part	86MRT-RCTD321-HA	7RR971896C	86MRT-RCTD321-HA
CA45678	86MRT	RCTD321	HA	F	Add Part	86MRT-RCTD321-HA	7RR971896C	86MRT-RCTD321-HA

In this scenario, the last column should be NULL as we do not have a row in between those "F" where action is "U". There might be some scenarios like this and sometimes the order might also be messed up. Both most of the times, I can fix the order.

[Raj · Accepted solution]

@ssripat3 Use this condition.

IF [External Part Action] in ('N','F') and [Row-1:External Part Action] != "F"
THEN [Row-1:Part Number]
ELSE NULL()
ENDIF

[ssripat3]

@Raj. Thank you for helping me with the updated formula and it works as expected.

[Raj]

@ssripat3 my pleasure !