Good Morning ALteryx ACE/Alteryx Lovers
I am facing challenge in mapping my final data with other mapping Table. Can some one please help me getting the Desired output:
Mapping Table :
Actual Data
Requirement:
Based on mapping Table, I need to add three columns (L1 Member, L2 member and L3 member) on my existing data source and show their respective values .Now the tricky part here is to is to check individual Columns (ID 1,RC Level 1,ID 2,RC Level 2 and So on) respectively. For example :
When there is any value in
ID 1,RC Level 1 say $C and Corporate - L1 Member - SAM
ID 2,RC Level 2 say $CA and Corporate Audit - L2 Member - ALICE
ID 3,RC Level 3 say $CAA and Corporate Advisory - L2 Member - JESSICA
Point to be Noted :So here mapping table data should check each column one of the original data source one after the another and assigning the correct Value .For example in Row 1 Actual data has columns ID 1 and level and if ID1 has Value then their respective L1 will come,IF ID 2 is their then respective L2 will come (Provided the L1 is same for ID 1 and Level 1 is same Like SAM) and IF ID 3 is there their then respective L3 will come (Provided the L1 and L2 are same for ID 2 and Level 2 Like SAM and ALICE)
As you can see in the highlighted example - L3 Member is JOHN not JACKY
Attaching the dataset for your reference.
My alteryx Version : 2018.3
Please let me know the correct way to map . In my dataset there are roughly around 30 Million rows are data which needs to be mapped
Expected Output :
Record ID | ACCOUNT | REGION | CITY | VALUE | ID_1 | RC_Level 1 | ID_2 | RC_Level 2 | ID_3 | RC_Level 3 | ID_4 | RC_Level 4 | L1 Member | L2 Member | L3 Member |
1 | 90234318899 | VN | Vienna | 6537 | $C | Corporate | $CA | Corporate Audit | NULL | NULL | NULL | NULL | SAM | ALICE | NULL |
2 | 36093727560 | LA | Lagos | 8198 | $C | Corporate | $CA | Corporate Audit | $CAA | Corporate Advisory | NULL | NULL | SAM | ALICE | JACKY |
3 | 14437491024 | CO | Bastia | 7405 | NULL | NULL | $M | Managing Fee | $CAA | Corporate Advisory | NULL | NULL | SAM | ALICE | JESSICA |
4 | 5774996410 | MI | Tarbes | 5244 | $C | Corporate | $M | Managing Fee | NULL | NULL | $SA | Serving Fee | SAM | VINCE | JOHN |
5 | 2309998564 | SS | Horsham | 2974 | $C | Corporate | $CA | Corporate Audit | $CAA | Corporate Advisory | $SA | Serving Fee | SAM | ALICE | JACKY |
Solved! Go to Solution.
hi @rajvivan11
Can you explain exactly why your row 3 is SAM, ALICE,JESSICA and not SAM, JOHN, JESSICA? The lookups here are $M-Managing Fee and $CAA-Corporate Advisory, neither of which have ALICE as a possible member.
Dan
Thanks @rajvivan11
Also for Row 3. What logic do you use to assign SAM as level 1? The level 1 ID is null
Dan
Hello @rajvivan11 ;
Actually, I cannot understand why row 3 is not SAM, VINCE, JESSICA. Because, the $M Managing Fee should say Level 2, VINCE?
Hi @rajvivan11,
Actually I am new in Alteryx so the flow can be optimized but it may help you. Could you please have a look?
Hope to help you!
Edited: Hi friend, I think there is some errors, I will correct and return you again. Sorry.