I am trying to insert key fields where a criteria/field matches: Source #1 A BCDE Year To DateYear To Date August 2022August 2022Account Account DescriptionC123P4 - XY Corp.C345P6 - XYZ Investment LLC 44123TOTALAcct 112345.6622345.6644124TOTALAcct 22345.6832345.6844125TOTALAcct 3 564.881564.88 FYear To DateAugust 2022C445P9 - BB LP32345.66332345.68 31564.88 Source # 2 IDCoAreaArea NumberCheckC123P4 - XY Corp.123P4ColbyP4xC345P6 - XYZ Investment LLC 345P6MooreP6yC445P9445P9RalphP9x Desired Outcome xy 123P4345P6 P4P6 ColbyMoore Year To DateYear To Date August 2022August 2022Account Account DescriptionC123P4 - XY Corp.C345P6 - XYZ Investment LLC 44123TOTALAcct 112345.6622345.6644124TOTALAcct 22345.6832345.6844125TOTALAcct 3 564.881564.88 x445P9P9RalphYear To DateAugust 2022C445P9- BB LP 32345.66332345.6831564.88 Any guidance is appreciated Thank you Desired Outcome Test.xlsx

Adding Rows When Criteria is met/Match

I am trying to insert key fields where a criteria/field matches:

Source #1

A	B	C	D	E
			Year To Date	Year To Date
			August 2022	August 2022
Account		Account Description	C123P4 - XY Corp.	C345P6 - XYZ Investment LLC
44123	TOTAL	Acct 1	12345.66	22345.66
44124	TOTAL	Acct 2	2345.68	32345.68
44125	TOTAL	Acct 3	564.88	1564.88

Year To Date

August 2022

C445P9 - BB LP

32345.66

332345.68

31564.88

Source # 2

ID	Co	Area	Area Number	Check
C123P4 - XY Corp.	123P4	Colby	P4	x
C345P6 - XYZ Investment LLC	345P6	Moore	P6	y
C445P9	445P9	Ralph	P9	x

Desired Outcome

			x	y
			123P4	345P6
			P4	P6
			Colby	Moore
			Year To Date	Year To Date
			August 2022	August 2022
Account		Account Description	C123P4 - XY Corp.	C345P6 - XYZ Investment LLC
44123	TOTAL	Acct 1	12345.66	22345.66
44124	TOTAL	Acct 2	2345.68	32345.68
44125	TOTAL	Acct 3	564.88	1564.88

445P9

Ralph

Year To Date

August 2022

C445P9- BB LP

32345.66

332345.68

31564.88

Any guidance is appreciated

Thank you

Desired Outcome Test.xlsx

Datasets

Tips and Tricks

Best Practices

Preparation

Accepted answers

Cicada_lies_heavy

Hi!

I approached it by transposing the second part of the header, joining it with Source #2, and retransposing the result. (I wished Alteryx had a one-tool transpose option....)

See the attached workflow for the intricacies (such as renaming the fields before retransposing in order to ensure the desired order of the header)

double_transpose_and_join.yxmd

Emmanuel_G

@NicoleHou

One way to achieve to this result.

Adding Rows When Criteria is met.yxmd

All comments

michelle_mathews

@NicoleHou , can you elaborate on the logic and criteria used for matching the two datasets?

NicoleHou

My apologies, I left out information on accident. Need to match based on row 4 in Source #1 (Columns D,E,F) and the ID column from Source #2

Source #1

A	B	C	D	E	F
			Year To Date	Year To Date	Year To Date
			August 2022	August 2022	August 2022
Account		Account Description	C123P4 - XY Corp.	C345P6 - XYZ Investment LLC	C445P9 - BB LP
44123	TOTAL	Acct 1	12345.66	22345.66	32345.66
44124	TOTAL	Acct 2	2345.68	32345.68	332345.68
44125	TOTAL	Acct 3	564.88	1564.88	31564.88

Source #2

ID	Co	Area	Area Number	Check
C123P4 - XY Corp.	123P4	Colby	P4	x
C345P6 - XYZ Investment LLC	345P6	Moore	P6	y
C445P9 - BB LP	445P9	Ralph	P9	x

Desired Output

			x	y	x
			123P4	345P6	445P9
			P4	P6	P9
			Colby	Moore	Ralph
			Year To Date	Year To Date	Year To Date
			August 2022	August 2022	August 2022
Account		Account Description	C123P4 - XY Corp.	C345P6 - XYZ Investment LLC	C445P9 - BB LP
44123	TOTAL	Acct 1	12345.66	22345.66	32345.66
44124	TOTAL	Acct 2	2345.68	32345.68	332345.68
44125	TOTAL	Acct 3	564.88	1564.88	31564.88

Desired Outcome Test.xlsx

Cicada_lies_heavy

Hi!

I approached it by transposing the second part of the header, joining it with Source #2, and retransposing the result. (I wished Alteryx had a one-tool transpose option....)