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: AccountText fieldManagerIDMain ManagerMain Manager ID009898ENTYry500007TRNEN /TYry000040TRNSi10608iv10710389456ENTYry500008TRNEN /TYry000040TRNsi5603iv5705389469ENTITY R0000253 DST ca11510iv9709389425R0000006DSTas38406iv10710389478ENTR0000007DSTp0132423iv10710389445EnTyR0000007DSTinp00258766iv2702389446ENTYry500007TRNEN /TYry000040TRNp00659444iv3703389447ENTYry500008TRNEN /TYry000040TRNp088594844iv3703389448ENTITY R0000253 DST p00258766iv2702389449R0000006DSTp092258iv1701389450ENTR0000007DSTiv1 701iv1 701389451EnTyR0000007DSTinca2598iv1701 Source2 Manager -2M-2 IDManager -1M1 IDManager 0M0 IDManager 1M1 IDManager 2M2 IDManager 3M3 IDWisdomManager 4M4 IDManager 5M5 IDfd3901 fh1801iv1701 p092258fd3901 fh2820iv1701 ca2598 p092258fd4902fm4850fh3832iv2702si2682ca4597das45462p00258766fd4902fm5851fh3832iv2702si12655ca4597das45462p00258766fd6903 fh4802iv3703si3685 as2588p00659444fd6903fm6856fh4802iv3703si19698 as8511p088594844fd7904 iv4704si4601ca6505ras32849p00719845612fd8905 iv5705si5603ca7506 as33401p00848465fd9906 iv6706si6604ca8507Eas34402p0091844fd10907 iv7707si7605ca9508 as35403p0108885fd11908 iv8708si8606ca10509Tas36404p011552269614fd12909 iv9709si9607ca11510 as37405p0124984163fd13910 iv10710si10608ca12511Eas38406p0132423fd14911 iv11711si11609ca13512 as39407p0149844

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

Common Use Cases

Accepted answers

danilang

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

ManagerInfo.yxmd

All comments

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

hierarchical-management.yxmd

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/422343#M78326

3) Process a hierarchy
Crawling Down an Org Structure

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

Chris

danilang

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

ManagerInfo.yxmd