Multiple Match condition

[gcosta]

Hi guys, hope you doing all good. I am trying to solve a problem and I would appreciate your help.

I have 2 lists. One with AccountID, Languages and Value. The other with Rep, Languages and Value Range (Start & End).

What I am trying to do is to look for Account language and value of list 1, and match with the possible Rep values on list 2 based on the Language AND Value Range.

This is an example of how the datasets looks like:

Thank you!!

[binuacs]

@gcosta One way of doing this with using the Generate Row tool, Using this tool you need to generate all the amounts between Start and End value and then join with the key fields Language and Value

[Qiu]

@gcosta 

I feel it is more a Append tool situation.
Append will create every possible combinations, and the data will increase rapidly though.

[danilang · Accepted solution]

hi @gcosta 

As @Qiu mentioned, there is the possibility for a row explosion when you use an append tool, since it automatically generates a dataset that is MxN rows.  This isn't likely to be an issue in your case, since the look is people-based and there aren't likely to be many sales reps.  If this does become a problem, you can use a divide and conquer technique.  First match on the unique ranges, then on the reps.  The workflow is more complex but this stops your intermediate datasets from ballooning too much, especially if there are many duplicate ranges in your look-up.

 Dan   

[gcosta]

Thank you so much @danilang @Qiu. Outstanding!! This looks great and do the job perfectly.

What I am trying to figure it out next is how I can select randomly only one person out of all possible ones (column Concat_rep) to assign the account. Is that something that can be done using Alteryx?

Thank you so much again!