Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

How to perform correct lookup by using Mapping Table

Highlighted
8 - Asteroid

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 :

 

Mapping 1.PNG

Actual Data

Mapping 2.PNG

 

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

Mapping Output.PNG

 

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 IDACCOUNTREGIONCITYVALUE ID_1RC_Level 1ID_2RC_Level 2ID_3RC_Level 3ID_4RC_Level 4L1 MemberL2 MemberL3 Member
190234318899VNVienna6537$CCorporate$CACorporate AuditNULLNULLNULLNULLSAMALICENULL
236093727560LALagos8198$CCorporate$CACorporate Audit$CAACorporate AdvisoryNULLNULLSAMALICEJACKY
314437491024COBastia7405NULLNULL$MManaging Fee$CAACorporate AdvisoryNULLNULLSAMALICEJESSICA
45774996410MITarbes5244$CCorporate$MManaging FeeNULLNULL$SAServing FeeSAMVINCEJOHN
52309998564SSHorsham2974$CCorporate$CACorporate Audit$CAACorporate Advisory$SAServing FeeSAMALICEJACKY
Highlighted
17 - Castor
17 - Castor

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

Highlighted
8 - Asteroid
I am extremely sorry for creating confusion. yes you are right, it should be sam Vince Jessica..while writing the output..i was making changes so that i can cover all possible scenarios but I think i missed some important points.$M is part of Level 2 so Vince will come .sorry for causing the unnecessary confusion .
Highlighted
17 - Castor
17 - Castor

Thanks @rajvivan11 

 

Also for Row 3.  What logic do you use to assign SAM as level 1?  The level 1 ID is null 

 

Dan

Highlighted
8 - Asteroid
As you can see .level 1 is fixed and it's always Sam..he is highest in the
hierarchy(CFO) and will always be SAM
Highlighted
8 - Asteroid
can some one please have a look and provide some techniques to solve this challenge
Thanks in advance
Highlighted
7 - Meteor

Hello @rajvivan11 ;

 

Actually, I cannot understand why row 3 is not SAM, VINCE, JESSICA. Because, the  $M Managing Fee should say Level 2, VINCE?

Highlighted
8 - Asteroid
I think while replicating the mapping Table i missed row 3 completely which is causing the issue .It should be sam Vince and Jessica

Highlighted
7 - Meteor

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.

Highlighted
7 - Meteor

Hello my friend @rajvivan11,

 

Please have a look this flow. Hope to help you.

Labels