Employee Hierarchy match with different table

[bharatm]

Hi,

New to ALteryx, I have two tables one has the employee details and the other has hierarchy details as shown below

Table A

EMPLOYEE ID	USERID	FirstName	LastNAme
123456	test1	IAM	TEST
245678	Test2	HELLO	TEST2
345789	Test3	Iam	LEGEND
458905	Test4	TERMINATOR	II

Table B

EMPLOYEE ID	USERID	LEVEL1	LEVEL2	LEVEL3	LEVEL4	LEVEL5	LEVEL 6	LEVEL 7	LEVEL 8	LEVEL 9	LEVEL 10
123456	test1	456789	789678	678956	null	null	null	null	null	null	null
245678	Test2	456789	789678	564789	897654	678956	null	null	null	null
345789	Test3	456789	567890	null	null	null	null
458905	Test4	456789	789678	678956	563456	879654	789603	342567	null	null
678956	Test5	456789	789678	null	null	null
789678	Test6	456789	null	null	null

how can I get the end table with USERID 's from table1 replace LEVEL 1 which contains EMPLOYEEID and add new column after each LEVEL with name added?

EMPLOYEE ID

USERID

LEVEL 1

NAME

LEVEL 2

NAME

LEVEL3

NAME

LEVEL 4

NAME

LEVEL 5

LEVEL 6

LEVEL 7

LEVEL 8

LEVEL 9

LEVEL 10

123456

test1

test100

test67

yupp3

245678

Test2

test100

test67

upoi1

tryme1

iamnew

[fmvizcaino]

Hi @bharatm ,

It is not clear to me what do you want to do in your output. Would you be able to fill your output table with example values from output2 as well for level1 and name columns?

Thanks,

Fernando V.

[bharatm]

Hi Fernando,

I want to match each EMPLOYEE ID in Table B(Hierarchy table) starting from LEVEL 1 and match it in Table A and create a new table with the USERID instead of employee ID and get the name added for each employee next to the USERID. If a match is not found in Table A - USER ID should be marked as "UNKNOWN".

I have not added all the entries in the sample Table A which are in Hierarchy Table.

EMPLOYEE ID	USERID	LEVEL1	LEVEL2	LEVEL3	LEVEL4	LEVEL5	LEVEL 6	LEVEL 7	LEVEL 8	LEVEL 9	LEVEL 10
123456	test1	456789	789678	678956	null	null	null	null	null	null	null
245678	Test2	456789	789678	564789	897654	678956	null	null	null	null

To explain in detail, let say for employee ID "123456" - I want to match LEVEL 1 entry "456789" with Table A and get the USERID and Name for that employee ID and append it to Row 1 (123456) in a new table, it should happen till it gets to null value.

EMPLOYEE ID

USER ID

LEVEL1

NAME1

LEVEL2

NAME2

LEVEL3

NAME3

LEVEL 4

NAME4

LEVEL 5

NAME5

LEVEL6

NAME6

LEVEL7

NAME7

LEVEL8

NAME8

123456

test1

test100

Iam CEO

test67

Iam svp

yuop3

Iam SDR

245678

test2

test100

Iam CEO

test67

Iam SVP

trespt2

I am SDR

tryme1

IAM DR

UNKNOWN

UNKNOWN

Hope its clear now?!

Thanks,

Bharat

[fmvizcaino]

Hi @bharatm ,

Since you are new to Alteryx, maybe the concepts used to accomplish the result you need is something you haven't seen yet.

I'm using an iterative macro that loops through your data to replace as you need. Inside the macro, there are some instructions showing how I got everything done.

To learn how to configurate an iterative macro, check the video below.

https://community.alteryx.com/t5/Videos/Build-Your-First-Iterative-Macro/td-p/55565

I'm sharing with you 2 files and please put both in the same folder.

[bharatm]

Thanks a ton Fernando. I will go through the tutorial.

[fmvizcaino]

Great @bharatm !!!

 If I achieve your expectations, please mark the answer as correct. 

And keep up the learning!!! 😄

Let me know if you have further questions.

Best,

Fernando Vizcaino

[danilang]

Hi @bharatm 

Interesting problem that you have, but it doesn't actually need the added complexity of the iterative macro.  You'd use the macro to build the hierarchy table, but since you already have that, you can perform the rest in a standard workflow

To give you

But, take the time to go through the tutorial and build sample macros.  It'll be useful in the future, both for building macros and being able to determine if you actually need to build one. 

Dan 

[bharatm]

Hi Dan,

The end table should be having corresponding user id's in place of employee id's. In Level 1(n) columns I should have the user id which is captured from Table A.

EMPLOYEE ID

USER ID

LEVEL1

NAME1

LEVEL2

NAME2

LEVEL3

NAME3

LEVEL 4

NAME4

LEVEL 5

NAME5

LEVEL6

NAME6

LEVEL7

NAME7

LEVEL8

NAME8

123456

test1

test100

Iam CEO

test67

Iam svp

yuop3

Iam SDR

245678

test2

test100

Iam CEO

test67

Iam SVP

trespt2

I am SDR

tryme1

IAM DR

UNKNOWN

UNKNOWN

Thanks,

Bharat

[danilang · Accepted solution]

Here you go @bharatm 

The values in the LEVELx columns are now the user IDs from Table A.  

Dan

[bharatm]

Thanks alot @danilang . Worked like a charm