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 |
Solved! Go to Solution.
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.
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
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.
Thanks a ton Fernando. I will go through the tutorial.
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
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
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
Thanks alot @danilang . Worked like a charm