AttributeRangeMapper Lookup table from excel

The SchemaMapper transformer will do exactly this, except... it doesn't work with ranges. So you would have to map each value individually.

Otherwise, it's the AttributeRangeMapper, rather than the AttributeValueMapper, that you'd want (though as you note it can't point to the Excel file dynamically).

My mind first went to the SchemaMapper, and then when I checked, ranges were a no go :(.

Yes, I guess you could read the data, "explode" the ranges, write it back to an Excel file, then use that new Excel file as a SchemaMapper source?! But the InlineQuerier is probably the best idea. I've been thinking it over for a while and I can't think of anything better.

Eh, I would probably do it in python, though I would use a csv file rather than an excel file for the reference file.

I'd probably do it in python as well, but if you're not too concerned about efficiency, you could use a featuremerger with duplicate suppliers set to yes and build a list of all the from to values on each feature, explode the list then test for if the value is between the from to value to only keep that result

Because I couldn't resist, I did the exploding part and made a hub transformer of it. I probably wouldn't recommend it here, but I figured it might come in handy for someone.

https://hub.safe.com/publishers/safe-lab/transformers/rangeexploder

That presupposes the values are integers.

I also thought about the schema mapper but as you've seen, there's no support for ranges.

Didn't think about this option. MIght be a good one. I'll look into it.

For what it's worth, I think the inline querier is a better option than my solution

You're right: I tried the InlineQuerier and it works perfect for this case!

Would you be able to upload your workspace and/or query? I'm trying to do this exact thing but haven't used the inline querier before.

@allyreynolds You just use a Cross Join to create a Cartesian Product of Table A * Table B = A.B, and use a WHERE to test if the value(s) in Table A are within any of the Lookup Ranges in Table B

• So, take Table A as say: ID, Value
• Take Table B as Say: MinRangeValue, MaxRangeValue, Classification

In SQL / InlineQuerier it just becomes,

SELECT A.*, B.Classification
FROM A CROSS JOIN B
WHERE A.Value>=B.MinRangeValue AND A.Value<B.MaxRangeValue

Depending on whether you are dealing with discrete (Eg. Integers) or indiscrete (Eg. Float) values may need to change whether you use > or >= and similarly < vs <=

The CROSS JOIN joins all possible permutations together of Table A and Table B, and the WHERE tests in that cartesian product (matrix) for where the Value intersects a Test Range.

Eg.

ID| Value| MinRangeValue| MaxRangeValue|Classification| Cartesian Intersection

1 | 0.5    |  0                        |  5                         |  "0-5"             | TRUE

1 | 0.5    |  5                        |  10                       |  "5-10"           | FALSE

2 | 6.7    |  0                        |  5                        |  "0-5"              | FALSE

2 | 6.7    |  5                        |  10                       |  "5-10"           | TRUE

Simple workspace attached, no cross join used in the inlinequerier, I'm not sure what difference it makes @bwn is probably better to explain if it matters or not

select 
    A.value, 
    B.score
from 
    Results A,
    Lookup B
where 
    A.value >=B."from" 
and 
    A.value < b."to"

Range_lookup.fmwt

select 
    A.value, 
    B.score
from 
    Results A,
    Lookup B
where 
    A.value >=B."from" 
and 
    A.value < b."to"

I only put the CROSS JOIN syntax for semantics as the descriptor of the actual process the SQL the InlineQuerier was following, just to make it clear, otherwise ironically your variant is the same syntax I first got used to when my introduction to SQL and GIS was through Mapinfo where similarly you could cross join Tables in it just by listing it as "FROM Table A, Table B, Table C" in its query tool! (and wow do I miss this in ArcGIS).

Thank you so much!