Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Join with numeric comparison

felipeecst
7 - Meteor

Hi everyone,

 

A few weeks ago I had this question, for which @jdunkerley79 suggested a great solution.

Now I have the same problem, but with a slight difference: two new columns, Salary Range (min) and Salary Range (max), that must be considered in the join. So, my table A now looks like this:

 

BenefitCompanyLocationUnitTrackLevelSalary Range (min)Salary Range (max)Value
Transport     100019991830
Transport     20003999 
Transport     4000  
Medical       8241
Car Plan    5  26217
Car Plan    6  23222
Car Plan    7  21054
Car Plan    8  18998
Lunch LocA     25
Lunch LocB     26
Lunch LocC     32

 

 

On the other table, as I said in the other post, I have all the combinantios for the columns Company, Location, Unit, Track and Level. But now I also have a new column, Salary, which I want to be considered in the join.

 

My idea is to keep the same behavior described on the previous post (joining with nulls) and also be able to match using the Salary ranges. For example, if the Salary Range (min) is 1000 and Salary Range (max) is 1999 on the first table, a row on the second table should only be joined if the value of it's Salary column is between 1000 and 1999. If one of the Salary Range columns is empty, then it should consider that it have no limit: for example, if Salary Range (min) is empty and Salary Range (max) has a value, we have only a upper limit. And if we don't have information neither for min or max, we should consider only the other columns on the join.

 

Can anyone help me solving this problem?

 

Thanks in advance!

6 REPLIES 6
felipeecst
7 - Meteor

Hi @Federica_FF, thanks for the reply.

From what I understand, that solution wouldn't work for continuous values, which is my case. 

 

jdunkerley79
ACE Emeritus
ACE Emeritus

I would use the old solution to get the first join.

 

After that you will have the Salary range on the row. You can then use a Filter to pick out the row which matches, i.e. a custom filter like:

(IsNull([Salary Range (Min)]) OR ([Salary] >= [Salary Range (min)]))
AND
(IsNull([Salary Range (Max)]) OR ([Salary] <= [Salary Range (max)]))

 Updated sample attached

felipeecst
7 - Meteor

That should do it. Thanks again! :)

jdunkerley79
ACE Emeritus
ACE Emeritus
You're welcome. Original question was a great fun puzzle - always learn myself when answering.
Inactive User
Not applicable

Hello Everyone 

 

All the solutions were great. But i have a solid variation in this. 

My sheet 1 has the amount field and sheet 2 has multiple ranges of amounts. 

For example 

Amount is 1001 - It needs to be matched against multiple ranges in sheet to that has values such as 

0-12000

12000-20000

20000-40000

Do we give the same approach here as well. I hope my made it clear. 

Labels