Skip to main content
Solved

How can I perform a two dimensional lookup from an external excel sheet?

osasfrer
Contributor
Forum|alt.badge.img

For example:

 

I have an external table that has regions for column headings (North, South, East, West) and part numbers for row headings (Part#001, Part#002, Part#003, Part #004,...). A part number in a specific region has a cost as shown in the in the image.

 

Also, I have another table with the attributes Region and Part Number in FME.

 

I want to make a new attribute that searches the external table based on the two attributes in my FME table: region and part number and returns the cost. How can I do this?

 

Annotation 2023-12-18 152500 

 

 

Best answer by geomancer

This can be done with just a FeatureJoiner and an AttributeDereferencer.

Lookup_Table

View original
Did this help you find an answer to your question?

4 replies

joepk
Influencer
Forum|alt.badge.img+20
  • joepkInfluencer
  • Influencer
  • December 19, 2023

Hi,

If you make sure your regions are seen as attributes (by e.g. setting row 2 as header in your Reader settings). You can use a Tester for the part numbers and maybe an AttributeKeeper for the regions? The Tester will return one feature/record and the AttributeKeeper will remove all regions you are not interested in.

Hope this helps

geomancer
Evangelist
Forum|alt.badge.img+47
  • geomancerEvangelist
  • Evangelist
  • Best Answer
  • December 19, 2023

This can be done with just a FeatureJoiner and an AttributeDereferencer.

Lookup_Table

osasfrer
Contributor
Forum|alt.badge.img
  • osasfrerContributor
  • Author
  • Contributor
  • December 21, 2023
geomancer wrote:

This can be done with just a FeatureJoiner and an AttributeDereferencer.

Lookup_Table

Thanks, it worked

osasfrer
Contributor
Forum|alt.badge.img
  • osasfrerContributor
  • Author
  • Contributor
  • December 21, 2023
joepk wrote:

Hi,

If you make sure your regions are seen as attributes (by e.g. setting row 2 as header in your Reader settings). You can use a Tester for the part numbers and maybe an AttributeKeeper for the regions? The Tester will return one feature/record and the AttributeKeeper will remove all regions you are not interested in.

Hope this helps

I want all the records to be displayed all at once in a table

Reply


Helpful Members This Week

Community Stats

30,912
Posts
117,123
Replies
38,714
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.