Alteryx Designer Discussions

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

How to perform correct lookup by using Mapping Table

rajvivan11
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
20 REPLIES 20
rajvivan11
8 - Asteroid
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
7 - Meteor

Hi @rajvivan11,

 

Of course, I use the version of 2019.2.5.62472.

rajvivan11
8 - Asteroid
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
7 - Meteor

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

rajvivan11
8 - Asteroid
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
8 - Asteroid
Can anyone provide any help on this? Still struggling go get the desired outcome
fharper
12 - Quasar

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
8 - Asteroid
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
12 - Quasar

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.

clipboard_image_0.png

fharper
12 - Quasar

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.

Labels