Skip to main content
Solved

Extract a variable from an Excel table based on another variable

robertr
Contributor
Forum|alt.badge.img+6

Hello all, 

Tried this several times but no luck. Currently, I have too many variables:

$mont_nbr $month_name $person_nbr $person_name (and a few more...)

In several cases I need the names, in other a number and so on. The idea is that I need all these variables throughout the workbench.

The thing is, if I have excel data like:

01 | January | 240 | Mike |

I would like the user to input the month_nbr (in this case, 01) and then bring in the other variables from the excel table., as for 01 it will always be January, 240 and Mike as the variables.

I am trying to find a way to ease the user input process. I Looked at the workspace runner but I just cannot make this work.

Best answer by takashi

Hi Robert again,

 

 

Two approaches have come to my mind. assume the Excel table has 4 fields: Month Nbr Month Name Person Nbr Person Name   1. Using the WorkspaceRunner 1) Define 4 published parameters - e.g. month_nbr, month_name, person_nbr, person_name - in the main workspace. 2) Create another workspace and define a published parameter - e.g. month_nbr. 3) Read the Excel table, select a record whose "Month Nbr" is matching with the parameter value ($month_nbr), pass the field values of the record to the main workspace as parameters and execute it through the WorkspaceRunner.

 

  2. Using the Joiner 1) Define a published parameter - e.g. month_nbr - in the main workspace. 2) Add the parameter value to features as an attribute through the ParameterFetcher. 3) Using the Joiner, read the Excel table and join features and the table record on the month number. Other field values (month name etc.) can be added to the features as new attributes.

 

 

 

 

Takashi
View original
Did this help you find an answer to your question?
This post is closed to further activity.
It may be a question with a best answer, an implemented idea, or just a post needing no comment.
If you have a follow-up or related question, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

5 replies

takashi
Influencer
  • takashiInfluencer
  • Best Answer
  • June 12, 2013
Hi Robert again,

 

 

Two approaches have come to my mind. assume the Excel table has 4 fields: Month Nbr Month Name Person Nbr Person Name   1. Using the WorkspaceRunner 1) Define 4 published parameters - e.g. month_nbr, month_name, person_nbr, person_name - in the main workspace. 2) Create another workspace and define a published parameter - e.g. month_nbr. 3) Read the Excel table, select a record whose "Month Nbr" is matching with the parameter value ($month_nbr), pass the field values of the record to the main workspace as parameters and execute it through the WorkspaceRunner.

 

  2. Using the Joiner 1) Define a published parameter - e.g. month_nbr - in the main workspace. 2) Add the parameter value to features as an attribute through the ParameterFetcher. 3) Using the Joiner, read the Excel table and join features and the table record on the month number. Other field values (month name etc.) can be added to the features as new attributes.

 

 

 

 

Takashi
robertr
Contributor
Forum|alt.badge.img+6
  • robertrContributor
  • Author
  • Contributor
  • June 12, 2013

Hello Takashi,

You are spot on. That's the concept. I will ge to work and come back with the outcome.

The help is much appreciated

robertr
Contributor
Forum|alt.badge.img+6
  • robertrContributor
  • Author
  • Contributor
  • June 14, 2013

Hello, 

I tried the Joiner feature: creator->parameterFetcher->Joiner-> ??

It all came out like in the example but I cannot pass on the resulted values; The joiner cannot be linked to the reader (It does not allow that as the source dataset for the reader is a publised parameter). I need the values from the Joiner to be placed in the source path, not as an attribute.

So, based on _month_nbr I extract Month_name and person_name. Ok, I need those two to be yet again published params (aka $(month_name) and @(person_name))

These 3 variables should form the file path of the reader. 

So, before I had the reader path like:

C:\\test\\$(month_nbr)\\$(month_name)_$(person_name).shp 

cmd.exe:

C:\\fme.exe fme_workspace.fmw --month_nbr 01 --month_name January --person_name John

The purpose is to shorten the cmd.exe input and still be able to construct the path:

C:\\fme.exe fme_workspace.fmw --month_nbr 01 

takashi
Influencer
  • takashiInfluencer
  • June 15, 2013
Hi,

 

 

For that purpose, move those transformers to another workspace and call the main workspace through the WorkspaceRunner: Creator->PrameterFetcher->Joiner->WorksapceRunner

 

In addition, you can also create a path string by concatenating the attributes beforehand and pass it to the main workspace as a parameter (source path).

 

  Takashi
takashi
Influencer
  • takashiInfluencer
  • June 15, 2013
An example:

 

* Replace ? in the image with \\. A back slash is displayed as ? in the Japanese environment.

 

 

 

Takashi

 

 

Helpful Members This Week

Community Stats

31,299
Posts
118,689
Replies
38,878
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.