join and Mapping ratio multiple to one then join again?

[rockeylearning]

Hello Alteryx Community!

I have a challenging with Join and Mapping, I hope you can shed some lights on how I can accomplishing this..

I have two different files (A and B), file A have 8 fields, they are numeric. File B have to fields (1 and 2). I need to join both files together, but mapping all 8 fields from File A to File B to field 1 to get a field 2 value for each of the fields in File A.

File 1

Group 2	Group 3	Group 4	Group 5	Group 6	Group 7	Group 8
1000
2000
4000	3000
		5000
		6000	7000
				8000

File 2

Group Name	ID	Description
Group 2	1000	This is a test 1
Group 2	2000	This is a test 2
Group 2	4000	This is a test 3
Group 3	3000	This is a test 4
Group 4	5000	This is a test 5
Group 4	6000	This is a test 6
Group 5	7000	This is a test 7
Group 6	8000	This is a test 8
Group 7		This is a test 9
Group 8		This is a test 10

Expected Result

Group 2	Group 2 Description	Group 3	Group 3 Description	Group 4	Group 4 Description	Group 5	Group 5 Description	Group 6	Group 6 Description	Group 7	Group 8
1000	This is a test 1
2000	This is a test 2
4000	This is a test 3	3000	This is a test 4
				5000	This is a test 5
				6000	This is a test 6	7000	This is a test 7
								8000	This is a test 8

I attempted to try below method, but ended up with million of rows

Greatly appreciate if you have an input how I can accomplish this.

Thank you!

[Felipe_Ribeir0 · Accepted solution]

Hi @rockeylearning 

See if this is it:

[rockeylearning]

@Felipe_Ribeir0 Thank you, this help, it does output the columns I needed. Though, question, when put this together with the real data, original data I have around 200K row, when ran the workflow, it returned 600K, why does it output that many rows if the mapping is consistent with the ID/name?

Thanks again

[Felipe_Ribeir0]

Hi @rockeylearning 

Its hard to guess without seeing the data, but i would say for you to take a look at the J anchor of the JOIN, and see if its there that you are getting 600k rows. If it is, you have duplicated Group Name/Id's combinations inside of your dataset. Find them and think about what you need to do with it - maybe remove duplicates, maybe keep it as it is, etc.

[Raj]

Just add the sort tool to it to achieve the desired result 
(to make it into waterfall look)

Group_6	Ascending
Group_5	Ascending
Group_4	Ascending
Group_3	Ascending
Group_2	Ascending