attribute value mapping over multiple "source attributes"

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

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.

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.

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.

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!

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.

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!

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.

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.

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.

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