Skip to main content
Question

Returning a value from a range from another data table

C
Forum|alt.badge.img

Hello,

I'm trying to run the equivalent of an excel 'index match' formula, trying to return the 'Category Code' from a secondary table, that looks like this:

Table A:

AreaLine RefYards FromYards ToCatagory CodeAAA1A00210002150019AAA1A00215002200088AAA1A00220002250035AAA1B00210002150045AAA1B00215002200087AAA1B00220002250057BBB1A00210002150064BBB1A00215002200013BBB1A00220002250001BBB1B00210002150098BBB1B00215002200020BBB1B00220002250030

From table B I am trying to return the relevant category that matches the following record:

AreaLine RefYards FromYards ToCategory CodeBBB1A002189921899

Within the range above, I would want to return category code '13'.

Having looked around on the forums, I think I need to use an 'InlineQuerier'; I wouldn't know how to produce the SQL for what I need though. Any help would be massively appreciated!

12 replies

nathanatsafe
Safer
Forum|alt.badge.img+7

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

takashi
Contributor
Forum|alt.badge.img+19
  • takashiContributor
  • Contributor
  • October 6, 2017

Do the "Yards From" and "Yards To" in the table B always have an identical value like your example?

danilo_fme
Evangelist
Forum|alt.badge.img+41
  • danilo_fmeEvangelist
  • Evangelist
  • October 6, 2017

Hi @NathanAtSafe,

Good job!

C
Forum|alt.badge.img
  • cjones28
  • Author
  • October 6, 2017
takashi wrote:

Do the "Yards From" and "Yards To" in the table B always have an identical value like your example?

They should yes

 

 

takashi
Contributor
Forum|alt.badge.img+19
  • takashiContributor
  • Contributor
  • October 6, 2017
takashi wrote:

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"
 
takashi
Contributor
Forum|alt.badge.img+19
  • takashiContributor
  • Contributor
  • October 6, 2017
takashi wrote:
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".
nathanatsafe
Safer
Forum|alt.badge.img+7
danilo_fme wrote:

Hi @NathanAtSafe,

Good job!

Thanks @danilo_inovacao :D
C
Forum|alt.badge.img
  • cjones28
  • Author
  • October 13, 2017

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

0684Q00000ArKUaQAN.png

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.

takashi
Contributor
Forum|alt.badge.img+19
  • takashiContributor
  • Contributor
  • October 13, 2017
cjones28 wrote:

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

0684Q00000ArKUaQAN.png

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?

 

 

gio
Contributor
Forum|alt.badge.img+15
  • gioContributor
  • Contributor
  • October 14, 2017

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

 

gio
Contributor
Forum|alt.badge.img+15
  • gioContributor
  • Contributor
  • October 14, 2017

@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 "

C
Forum|alt.badge.img
  • cjones28
  • Author
  • October 16, 2017
gio wrote:

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

 

Reply


Helpful Members This Week

Community Stats

30,907
Posts
117,105
Replies
38,708
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.