Hi @cjones28, thanks for posting your question!
I think the InlineQuerier in FME is a great alternative to an Excel formula for this type of analysis. If you have the two tables (TableA, TableB) routed into the Connect Input port of the InlineQuerier, I believe an SQL statement like this should work:
SELECT TableA.CategoryCode // this tells the query to return only the Category
FROM TableA // Code from Table A
WHERE TableB.YardsFrom >= TableA.YardsFrom AND TableB.YardsTo <= TableA.YardsTo
// this statement involves two conditions: TableB YardsFrom and YardsTo must
// be matched with the appropriate range from TableA.
If you want to use more matching conditions, just add more ANDs to your WHERE clause.
SQL is super powerful and can be used all over the place in FME (if you want ;)). If you're new to it, I suggest taking a look at
these tutorials. It's how I got started with it!
Thanks,
Nathan
Do the "Yards From" and "Yards To" in the table B always have an identical value like your example?
Hi @NathanAtSafe,
Good job!
Do the "Yards From" and "Yards To" in the table B always have an identical value like your example?
They should yes
Do the "Yards From" and "Yards To" in the table B always have an identical value like your example?
OK. This SQL query could generate your desired result.
select
b.*,
a."Category Code"
from "TableB" as b
left outer join "TableA" as a
on b."Area" = a."Area"
and b."Line Ref" = a."Line Ref"
and a."Yards From" <= b."Yards From"
and b."Yards To" < a."Yards To"
OK. This SQL query could generate your desired result.
select
b.*,
a."Category Code"
from "TableB" as b
left outer join "TableA" as a
on b."Area" = a."Area"
and b."Line Ref" = a."Line Ref"
and a."Yards From" <= b."Yards From"
and b."Yards To" < a."Yards To"
Assume that TableB doesn't have the attribute "Category Code".
Hi @NathanAtSafe,
Good job!
Thanks @danilo_inovacao
Thank you @NathanAtSafe and @takashi for your answers; I've used the SQL as suggested via the InlineQuerier, but unfortunately it hasn't produced the expected result; I'm only expecting 22644 (Table A records looking up a track category) as below, but it is giving me 256337 (amounting to all records in Table B, where I'm reading track category from.)
The SQL used as as follows ('ELR' is what I previously referred to as 'Area', and a 'Track ID' is what I referred to as 'Line Ref'):
select a."EQUIP_NO", b."TRACK_CATEGORY"
from "TableA" as a
left outer join "TableB" as b
on a."ELR" = b."ELR"
and a."Track ID" = b."REF_TRACKID"
and a."Start Mileage" >= b."ELR_STARTMEASURE"
and a."End Mileage" <= b."ELR_ENDMEASURE"
The 256337 records that do pass through the InlineQuerier SQL subsequently create no output values when connected to a writer for Excel.
Any assistance you can provide will be appreciated.
Thank you @NathanAtSafe and @takashi for your answers; I've used the SQL as suggested via the InlineQuerier, but unfortunately it hasn't produced the expected result; I'm only expecting 22644 (Table A records looking up a track category) as below, but it is giving me 256337 (amounting to all records in Table B, where I'm reading track category from.)
The SQL used as as follows ('ELR' is what I previously referred to as 'Area', and a 'Track ID' is what I referred to as 'Line Ref'):
select a."EQUIP_NO", b."TRACK_CATEGORY"
from "TableA" as a
left outer join "TableB" as b
on a."ELR" = b."ELR"
and a."Track ID" = b."REF_TRACKID"
and a."Start Mileage" >= b."ELR_STARTMEASURE"
and a."End Mileage" <= b."ELR_ENDMEASURE"
The 256337 records that do pass through the InlineQuerier SQL subsequently create no output values when connected to a writer for Excel.
Any assistance you can provide will be appreciated.
Your SQL statement performs LEFT OUTER joining TalbeB to TableA, so it's natural that the number of resulting records is equal to or greater than the number of records from the TableA.
Actually I'm not sure what you intend to do, since the source data schema is different from the example tables that you have posted at first. A and B have been reversed?
@cjones28
you can merge the B list to A list on Area and Line Ref and the use an attributecreator.
Catagory Code
=
((@Value(req_Yards From)>=@Value(Yards From ))&&(@Value(req_Yards To)<=@Value(Yards To )))==1?@Value(Catagory Code):0
Filter, remove atributes and rename like this.
@cjones28
If you want to use InlineQuerier
The query is
select A."Area ",A."Line Ref ",B."Yards From",B."Yards To",A."Catagory Code"
from "CSV" A,"Output" B
where A."Area "=B."Area" and A."Line Ref " = B."Line Ref"
and B."Yards From" >= A."Yards From "
and B."Yards To" <= A."Yards To "
@cjones28
If you want to use InlineQuerier
The query is
select A."Area ",A."Line Ref ",B."Yards From",B."Yards To",A."Catagory Code"
from "CSV" A,"Output" B
where A."Area "=B."Area" and A."Line Ref " = B."Line Ref"
and B."Yards From" >= A."Yards From "
and B."Yards To" <= A."Yards To "
Thanks @Gio, the problem I'm now encountering is both the 'Line Ref', 'Yards From' and 'Yards To' are held in the database as text values, even though they are numeric. Line ref in '0000' format, and 'Yards To' and 'Yards From' are held in '000.0000' format.
As a consequence (I assume), the output of the 'InlineQuerier' is returning the 'Area' and 'Line Ref', but neither yard measurements. I've had a go at converting them through a 'StringFormatter', but so far it's not been successful.
Any assistance would be most appreciated. :)
