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
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
7 - Meteor

Hi @rajvivan11,

 

Of course, I use the version of 2019.2.5.62472.

Highlighted
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
Highlighted
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

Highlighted
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
Highlighted
8 - Asteroid
Can anyone provide any help on this? Still struggling go get the desired outcome
Highlighted
11 - Bolide

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.

 

Highlighted
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
Highlighted
11 - Bolide

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

Highlighted
11 - Bolide

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