Hierachial Search

[pranee_007]

I am trying to create two new columns that are underlined below using the data from Source2.

Facts about Source2:

1. It is a hierarchy model. Main Manager column we are trying to create will always be equal to Manger 1 in Source 2.

2. Source 2 can have different Manager 5 and same Manager 1 (Example: iv3 has different Manager 5)

3. Source 2 can have same Manager 6 and same Manager 1 (Example: iv1 and iv2)

Conditions:

1. If Manager in Main file is found in Manager 2 or 3 or 4 or 5, I want the Main manager field to be populated with corresponding Manager 1 from Source 2.

2. If Manager in Main file is found in Manager 1, return the same.

3. If Manager in Main file is found multiple times, I want Manager 1 to be populated only once as it will be same for the same Manager.

I want the output as shown below in 'main file'. Please help me build the workflow for the same. Please let me know if you have any questions. Thank you so much in advance.

Main file:

Account	Text field	Manager	ID	Main Manager	Main Manager ID
009898	ENTYry500007TRNEN                             /TYry000040TRN	Si10	608	iv10	710
389456	ENTYry500008TRNEN                             /TYry000040TRN	si5	603	iv5	705
389469	ENTITY R0000253 DST	ca11	510	iv9	709
389425	R0000006DST	as38	406	iv10	710
389478	ENTR0000007DST	p013	2423	iv10	710
389445	EnTyR0000007DSTin	p002	58766	iv2	702
389446	ENTYry500007TRNEN                             /TYry000040TRN	p006	59444	iv3	703
389447	ENTYry500008TRNEN                             /TYry000040TRN	p088	594844	iv3	703
389448	ENTITY R0000253 DST	p002	58766	iv2	702
389449	R0000006DST	p092	258	iv1	701
389450	ENTR0000007DST	iv1	701	iv1	701
389451	EnTyR0000007DSTin	ca2	598	iv1	701

Source2

Manager -2	M-2 ID	Manager -1	M1 ID	Manager 0	M0 ID	Manager 1	M1 ID	Manager 2	M2 ID	Manager 3	M3 ID	Wisdom	Manager 4	M4 ID	Manager 5	M5 ID
fd3	901			fh1	801	iv1	701								p092	258
fd3	901			fh2	820	iv1	701			ca2	598				p092	258
fd4	902	fm4	850	fh3	832	iv2	702	si2	682	ca4	597	d	as45	462	p002	58766
fd4	902	fm5	851	fh3	832	iv2	702	si12	655	ca4	597	d	as45	462	p002	58766
fd6	903			fh4	802	iv3	703	si3	685				as2	588	p006	59444
fd6	903	fm6	856	fh4	802	iv3	703	si19	698				as8	511	p088	594844
fd7	904					iv4	704	si4	601	ca6	505	r	as32	849	p007	19845612
fd8	905					iv5	705	si5	603	ca7	506		as33	401	p008	48465
fd9	906					iv6	706	si6	604	ca8	507	E	as34	402	p009	1844
fd10	907					iv7	707	si7	605	ca9	508		as35	403	p010	8885
fd11	908					iv8	708	si8	606	ca10	509	T	as36	404	p011	552269614
fd12	909					iv9	709	si9	607	ca11	510		as37	405	p012	4984163
fd13	910					iv10	710	si10	608	ca12	511	E	as38	406	p013	2423
fd14	911					iv11	711	si11	609	ca13	512		as39	407	p014	9844

[GiuseppeC]

Hi @pranee_007,

this might be a sub-optimal yet simpler solution to implement. If the size of your files allows it, you could consider using the Append Fields tool to append all records/fields from Source2 to your Main file. At that point you'll get a table with all the combination of all rows from the 2 tables.

Having the data in this format will allow you to easily build a series or IF-THEN-ELSE statements to implement your conditions.

Finally, you could filter out the unnecessary rows.

Example attached.

Hope this helps, but let me know if you have any questions.

Best,

Giuseppe

[ChrisTX]

These related posts may help:

1) Alteryx Weekly Challenge #12:

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-12-Creating-an-HR-Hierarchy/td-p/36740

In this example, we have a series of records where you have an employee title, an ID to the title, and the number of the managers’ title ID.

2) Generate each tree branch for a hierarchy

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Multilevel-Product-Hierarchy/m-p/42234...

3) Process a hierarchy
Crawling Down an Org Structure

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Crawling-Down-an-Org-Structure/td-p/44...

Chris

[danilang · Accepted solution]

Hi @pranee_007 

For your particular case, you don't need to build a hierarchy since you have the managers table available.

Transpose the managers list to all mangers 1-5 in one column.  Join this to the main list to get the main manager fro each row.  Take only the unique rows from this list giving you

 

Dan