How to perform correct lookup by using Mapping Table

[rajvivan11]

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

[rajvivan11]

First of all thanks for your quick turnaround..
I will check in an hour and update you shortly.
May I know which version you used for creating the workflow.Getting some error..i can fix it by making changes Via XML code but thought of checking before implementing this

[EceSimsek]

Hi @rajvivan11,

Of course, I use the version of 2019.2.5.62472.

[rajvivan11]

If it is not too much to ask, can you add a screenshot here so that i can quickly have a look .I need to install latest version to open the workbook

[EceSimsek]

There is no problem, I am sharing with you the workflow process below. Please let me know if you need any information.

[rajvivan11]

Thanks .Truly appreciate it but I am looking more like a dynamic approach .We have 52 unique names in L1 , L2 and L3 we cannot map it mannually. it has to come via dynamic transforming and joining

[rajvivan11]

Can anyone provide any help on this? Still struggling go get the desired outcome

[fharper]

A couple of thoughts on using the community.  And please know I say this as a general advice to all newer users in the community and mean no disrespect as I have been guilty as well.  

If you seek help try to be clear and accurate in your description of the issue as well as your expectation for a result.  You came here for an answer you could not get on your own and others in the industry are very generous with their time to respond but you essentially waste time for you and them if you do not clarify properly.  If you see you made a mistake in framing the issue or expectation of result them go back and edit you post...you can click on the 3 dots in upper right to get options and one is to edit your post so you can correct errors.  after a few hours you lose ability to edit but can post an update so everyone has the correct info and isn't giving great advice on the wrong thing.

That said @EceSimsek has a solution that looks dynamic but I think is a little off of what you really want but I can't be sure because things are not so clear.  One of my concerns is L1, L2, L3 vs Level 1, Level 2 and Level 3 etc in the data.  In my mind I see these as synonymous but the data as presented doesn't really reflect that.  SO my perception of our problem is that you need to match all three people, L1-L3 to each ID* and RC_Level* in the data iteratively.

To clarify

1. [ID_* in the Data file is a key match to [ID] in the Mapping file
2. [RC_Level*] in the Data file is a key match to [Description] in the Mapping file
3. I see no value in the column [Level] in the mapping file.
4. L1 thru L3 is the result you want to include when #1 and #2 above are a match.

The flow would be very similar to  that which @EceSimsek provided if there are a finite number of ID iterations in the data....I see ID_1 thru ID_4.

So join the data to the Mapping on ID_1 and RC_Level1 to ID and Description and select only the L1_Member thru L3_Member columns from the right input stream. rename as you desire then Union Left Join with Join and flow into the next join, nearly a clone of prior, to Mapping again but the left join columns are the next iteration (ID_2 & RC_Level2) and so on.

You end up with your initial data file with 12 new columns populated based on the joins.  the 12 columns are the L1_Member thru L3_Member columns from each of the 4 joins.  where no join was made the columns exist but would be null().

I would give you a flow but I am in between jobs and do not have a copy of Desktop available.  However I think this explanation should suffice.

FYI I used the "edit reply" option 3 times to add info and correct spelling...it is a great option many are not aware of.  

Hope this helps.

[rajvivan11]

I completely understand but I am completely new to this community..i was trying to make changes in my screenshot but couldn't able to get the Edit option/didnt find the edit option.But I did a mistake which I didn't notice until someone pointed it.The moment I realised i was making it sure to give enough information and apologised many times for causing the issue
The problem is we need to have a dynamic mapping because currently we have 35 unique names ( l1,l2 and L3) which is changing every quarter .People are moving to one function to other function.

Should I delete this post and repost my question to avoid any confusion.Kindly suggest

[fharper]

No Worries...Even those of us who have familiarity with Community get in a rush and do not provide clarity at times...both in posting for help and in giving help...just felt it a good time to through out there.

As I said I do not have Desktop available right now...working on a MacBook. But without a revision or update of the data and need I do not see where my prior would not address this as expressed.  

One thing I always like to ask is how will this be used...if not that then do another illustration of the desired outcome and be as exact as possible.

This is an illustration of the inputs and result if I understand you correctly and if you do what I proposed.

[fharper · Accepted solution]

Its not so much how many names but in your example the structure is to have up to 3 names per ID and Description combination.  that is why I say the clarity in describing or providing example data and in framing the need and outcome is so important.  having another 30 or 90 ID & Description combinations won't matter to the solution I provided...but if you have an unknown or "infinite" number of names to a given ID & Description it can still work but get very wide on the result.  if the number of ID and RC Level combinations, kind of the same as ID & Description combinations is variable up to some large number life gets uglier in how wide the row gets doing what I described.

Most reports consumed by a human should be more than a few pages and no wider than 30 columns probably...otherwise they are in data paralysis and can't easily see what is important if anything.

if you are feeding another process maybe that process simply needs adapted to find the one or two relational items it needs vs consuming all possible combinations...kind of goes back to the origins of why we started normalizing data.

Sorry...I digress...and being in-between assignments I have too much time to be verbose...

Thus knowing how this is used to dictate some action of decision is useful because what you think is what you want may only be a step on the way and possibly even an unnecessary step...if it is to find out what responsibilities a person has then I might simply do an App asking for the name or ID and then match it to the various roles and responsibilities described by the ID and description matches that one person would have.  versus building a huge matrix of all possible people and roles...

so consider what I and others have provided carefully and contrast that with what you think you need and how that result will be used and see if you need more help or maybe this is the answer..  If you need to redefine the question or problem I would at that point suggest closing this thread by selecting the the nearest solution answer so no one else tries to solve the "unsolvable" or unneeded. then post a carefully framed new issue with the best inputs and expectations you can describe.

We are all a bit nuts and I think love solving problems for people so I am sure you will get what you need.  Just consider if you need to repost with it reframed for clarity or if the answer is in the responses already received.