Join with numeric comparison

[felipeecst]

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:

Benefit	Company	Location	Unit	Track	Level	Salary Range (min)	Salary Range (max)	Value
Transport						1000	1999	1830
Transport						2000	3999
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!

[Federica_FF]

Try to see if it helps :http://community.alteryx.com/t5/Alteryx-Knowledge-Base/Alteryx-Weekly-Exercise-Week-1-Join-to-Range/...

[felipeecst]

Hi @Federica_FF, thanks for the reply.

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

[jdunkerley79 · Accepted solution]

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]

That should do it. Thanks again! :)

[jdunkerley79]

You're welcome. Original question was a great fun puzzle - always learn myself when answering.

[Inactive User]

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.