Skip to main content

Hi all,

I have in an excel sheet something like:

I would like to determine the score according to my attribute value. So for example I hav an attribute with value 10 the score would be 1,5.

The AttribueValueMapper could do this I know, but I would like to point to the excel so I can 'manipulate' the scores in excel and not in FME workbench.

Is there a way to achieve this?

 

Greetings,

Arno

I would consider an inlineQuerier.

 

 

Also upvote this idea https://knowledge.safe.com/content/idea/28819/enhance-featuremerger-comparison-modes-for-in-rang.html

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).


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 😞.


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.


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

 


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

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


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.


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).

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.


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


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!


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.


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

 

 


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.

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


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

In InlineQuerier it's the same query @ebygomm. SQLite will take that variant as an implicit statement of a Cross Join.  Other variants of the syntax by instead using JOIN, INNER JOIN, LEFT OUTER JOIN or no *JOIN* clause at all (like yours) execute identically whenever the following ON or USING is omitted.

 

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).


In InlineQuerier it's the same query @ebygomm. SQLite will take that variant as an implicit statement of a Cross Join. Other variants of the syntax by instead using JOIN, INNER JOIN, LEFT OUTER JOIN or no *JOIN* clause at all (like yours) execute identically whenever the following ON or USING is omitted.

 

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!


Reply