• EN
Solved

attribute value mapping over multiple "source attributes"

K
Badge

Hi, I have a table with values that I would like to map to different values via an external file. I can kind of make the AttributeValueMapper work the way I want, but only on one "source attribute" at a time. I've been trying to read about the SchemaMapper but the documentation is a little confusing and I'm not sure how to relate the help files to my example.

So if I have a table with multiple names and "M" columns like below:

And an external file with related values like below:

What is the easiest way to map the number values to the letter values of the external file to result like below?

Is the SchemaMapper the correct transformer for this? It seemed like it would need the attribute column names to match between the table and the external file, but I cannot rename the table headers. Seems like there should be an easy way to do this that I'm missing.

Hopefully I've explained this well enough, any help would be appreciated thanks!

icon

Best answer by ebygomm 21 July 2016, 19:59

View original

12 replies

todd_davis
Rank
Userlevel 1
Badge +12

I can't think of any really nice way at the moment. If you were to do this with the schemamapper, you are best to add an additional column to your external reference file that contains NUM, VALUE and the addition of FIELD.

your ref table might end up like this, as it need the schema defined for each field

Field

 

NumValueM11AM12BM13CM21AM22B

etc.

Now in schemamapper choose the table.

First create a "filter"...attribute name = Field attribute value = Num

Now a "New Attribute" ....attribute name = Field attribute value = Value

Basically saying "if M1 (field) = 1(Num) then M1 (field) = A (Value)"

That will do it. There are other ways of course such as attributeexloding...checking attribute name and performing an attributemapper and joining it all back together, or using things like the attributedereferencer, attributecreator etc but there are other bit and pieces around those that you would need.

G
Badge +3

seems that using a stringmapper in a attribute creator arithmic editor is more straightforward in this case.

[string map 1 A 2 B 3 C]

..not handy for large maps and or large amounts of atributes though.

erik_jan
Rank
Userlevel 2
Badge +16

If the shown example is complete, 3 StringReplacers could do the job, replacing (in all required attributes) 1 by A, 2 by B and 3 by C. But if the project is far more extensive than the example, this would not be a good solution.

K
Badge

I can't think of any really nice way at the moment. If you were to do this with the schemamapper, you are best to add an additional column to your external reference file that contains NUM, VALUE and the addition of FIELD.

your ref table might end up like this, as it need the schema defined for each field

Field

 

NumValueM11AM12BM13CM21AM22B

etc.

Now in schemamapper choose the table.

First create a "filter"...attribute name = Field attribute value = Num

Now a "New Attribute" ....attribute name = Field attribute value = Value

Basically saying "if M1 (field) = 1(Num) then M1 (field) = A (Value)"

That will do it. There are other ways of course such as attributeexloding...checking attribute name and performing an attributemapper and joining it all back together, or using things like the attributedereferencer, attributecreator etc but there are other bit and pieces around those that you would need.

Thanks for the reply Todd. This is what it seemed like I would need to do to use the SchemaMapper but I don't think that will work for me since both tables will be changing. The original table will be adding/removing "M" columns, adding name rows, and the ref table will be changing its "VAL" values. Modifying the ref table this much just to use the SchemaMapper started to feel like it was defeating the purpose of having a simple ref table. I'll look into the other methods you mentioned. Thanks!

K
Badge

seems that using a stringmapper in a attribute creator arithmic editor is more straightforward in this case.

[string map 1 A 2 B 3 C]

..not handy for large maps and or large amounts of atributes though.

Thanks Gio, this seems promising. I was looking at this method using AttributeManager, but it looked like I would need to copy the string map into each "M" attribute. Right now I only have 24 M columns but will be adding much more and the string mapping values will be changing also, so this would get tedious fast. Looking at the StringReplacer transformer it looked like it would allow me to select multiple attributes but only allow one match/replace at a time so this might lead to the same problem. Pleas let me know if I'm missing something here.

K
Badge

If the shown example is complete, 3 StringReplacers could do the job, replacing (in all required attributes) 1 by A, 2 by B and 3 by C. But if the project is far more extensive than the example, this would not be a good solution.

Hi Erik, unfortunately no the example is much smaller than the actual data. Right now my data table is 24 cols x 50 rows and will continue to grow in both axis, and the ref table is 2 columns x 20 rows, but will also be growing and the "VAL" values will be change a lot. StringReplacer seems like it would work but not as dynamic as I need. I need to be able to quickly change the "VAL" values and re-run.

Thanks for the reply!

erik_jan
Rank
Userlevel 2
Badge +16

Another possible solution:

Assuming your source table is in a database (you called it table) I would create a workspace to write the external table to the same database, replacing the values whenever needed.

The replace functionality in SQL allows you to join the tables in a SQL statement (preferably using SQL functions).

Then the FME transformer SQLCreator can be used to retrieve the transformed information using the joining SQL statement into FME.

K
Badge

A little more information, both my data table and my ref table will be growing and changing frequently so the solution needs to be dynamic or it just becomes too much work. SchemaMapper might not be the right direction. StringReplacer seems the closest right now, allows me to change multiple attributes at a time but only allows one value to be mapped at a time, requiring multiple transformers. AttributeValueMapper allows me to map all values, but to only one attribute at a time, requiring multiple transformers. Some kind of combination between StringReplacer and AttributeValueMapper would work well.

K
Badge

Another possible solution:

Assuming your source table is in a database (you called it table) I would create a workspace to write the external table to the same database, replacing the values whenever needed.

The replace functionality in SQL allows you to join the tables in a SQL statement (preferably using SQL functions).

Then the FME transformer SQLCreator can be used to retrieve the transformed information using the joining SQL statement into FME.

Both my data table and ref table are excel spreadsheets. A database would probably be a good format for this data but I'm just working with what I'm given. I don't have much experience with databases either. I wonder if i can use FME to generate a database, then run the SQL statement? Still seems overly complicated, but if its dynamic it should work.

ebygomm
Rank
Userlevel 1
Badge +21

Not sure how robust it is, but exploding the attributes,using a feature merger to map the values and then rebuilding the original attribute values appears to work.

So you explode the attributes to something like this

Feature-merge with your lookup table to get the replacement value, then recreate the attributes with the original names and aggregate together (with a few other bits to handle exposing the attributes

multi-attribute-value-map.zip

K
Badge

Not sure how robust it is, but exploding the attributes,using a feature merger to map the values and then rebuilding the original attribute values appears to work.

So you explode the attributes to something like this

Feature-merge with your lookup table to get the replacement value, then recreate the attributes with the original names and aggregate together (with a few other bits to handle exposing the attributes

multi-attribute-value-map.zip

Thanks egomm, this works! I appreciate everyone's help!

B
Badge

Not sure how robust it is, but exploding the attributes,using a feature merger to map the values and then rebuilding the original attribute values appears to work.

So you explode the attributes to something like this

Feature-merge with your lookup table to get the replacement value, then recreate the attributes with the original names and aggregate together (with a few other bits to handle exposing the attributes

multi-attribute-value-map.zip

This is a great beginning. I just have a few questions because it isn't quite doing what i want. Similar to this, i have a lookup table in excel which lists an ID column and a Acronym column. I'm exporting a shapefile from my SDE Oracle DB. I want to add a column and lookup the acronym from the lookup table to populate it. this process overwrites the id column which is a bad idea. I also noticed that i only have half the rows in the final output and the extra columns not used output is empty? any thought?

 

 

Reply


Community Stats

28,799
Posts
109,770
Replies
36,617
Members
Terms & ConditionsCookie 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.