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 IDPrefixBaseSuffixExternal Part ActionActionPart NumberExternal Part NumberAA12345A1CRT20A001EAUReplace PartA1CRT-20A001-EA1AB122135EAA12345A1CRT20A001EBNAdd PartA1CRT-20A001-EB1AB122135FBA9876545ART1020CAUReplace Part45ART-1020-CA3323330226BA9876545ART1020CAFAdd Part45ART-1020-CA3323330226BA9876545ART1020CAUReplace Part45ART-1020-CA3323330226BA9876545ART1020CAUReplace Part45ART-1020-CA3323330226CA4567896MRTANC123BAUReplace Part96MRT-ANC123-BA9AB445567HCA4567896MRTANC123BBWFAdd Part96MRT-ANC123-BBW9AB445567I Output Data Record IDPrefixBaseSuffixExternal Part ActionActionPart NumberExternal Part NumberReplaces Part NumberAA12345A1CRT20A001EAUReplace PartA1CRT-20A001-EA1AB122135E AA12345A1CRT20A001EBNAdd PartA1CRT-20A001-EB1AB122135FA1CRT-20A001-EABA9876545ART1020CAUReplace Part45ART-1020-CA3323330226 BA9876545ART1020CAFAdd Part45ART-1020-CA332333022645ART-1020-CABA9876545ART1020CAUReplace Part45ART-1020-CA3323330226 BA9876545ART1020CAUReplace Part45ART-1020-CA3323330226 CA4567896MRTANC123BAUReplace Part96MRT-ANC123-BA9AB445567H CA4567896MRTANC123BBWFAdd Part96MRT-ANC123-BBW9AB445567I96MRT-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? Sample Workflow for Replace Part Logic.yxmd

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

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?

Sample Workflow for Replace Part Logic.yxmd

Workflow

Tips and Tricks

Parse

Accepted answers

davidskaife

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

Sample Workflow for Replace Part Logic.yxmd

Raj

@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

All comments

davidskaife

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

Sample Workflow for Replace Part Logic.yxmd

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

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

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167