Looks like a case for the SchemaMapper transformer. There's a good tutorial here

https://knowledge.safe.com/articles/1136/schemamap...

Quite a challeging transformer if you're new to FME and may require the cross walk structure to be reformatted. The AttributeValueMapper could work but wouldn't use an external file for the definitions except to import from a file.

@daveyourguide Here's an example (below) FME Template with the AttributeValueMapper and SchemaMapper reading from your example (in Excel), writing to Excel workbook with multiple worksheets. Your example source data and the schema mapping lookup is included.

Essentially in the SchemaMapper you are using the values in the SchemaMapperLookup.csv and specifying:

Filter Features (where this attribute equals this value)

@mark_1spatial - thank you so much for the quick response, this is a great help. I just ran the demo and it worked like a charm. Next, I will apply this to my own data and let you know how it goes. Never occurred to me that I had to use both attribute mapper and schema mapper in tandem. Thanks! -Dave

@daveyourguide you don't need to use both! Choose whichever suits your needs. I included both to show you that it is possible either way.

AttributeValueMapper is the easy method without crosswalk file. The SchemaMapper is the slightly more complicated (but more powerful/generic) method using external file. With the external file you could simply add a new value in and the workspace would just work.

@mark_1spatial - Success! I managed to replicate your template with my data in a simple test with one field using the schema mapper. I haven't tried the attribute mapper yet because I wanted to try the complex solution out first. Very cool. Thanks for the help. As I dive into creating my final workflow I may have some more newbie questions for you. Thanks again!

Dave

Basically the AttributeValueMapper is for where the values in your crosswalk table never change (or change very infrequently) because changes would need the workspace to be edited. The SchemaMapper is for where the values in your crosswalk table are likely to change quite often. Then the changes are picked up automatically without editing the workspace.

i.e. AttributeValueMapper is static (hard-coded) whereas the SchemaMapper is flexible/dynamic

- your help has gotten me pretty far. I've successfully applied the template you provided to my data in a simple 1:1 field crosswalk test. I have run into problems adding complexity to workflow when I try to add a second field to the lookup table and create additional parameters. The first field crosswalks perfectly but the second field only crosswalks 36 of 4k+ values. I noted that the values crosswalked correspond to the adjacent values in the first field. I can see it is considering them but I don't know what the fix is. I have included screencaps and a modified template. Help!slide1.jpgslide2.jpgsource-2-target-schools.fmwt

@daveyourguide The schema mapping table needs to be organised in similar columns. You just treat everything in the same way. See attached template - I think it is doing what you want. By the way - you can also define the output schema in a similar way using a Dynamic Writer and use a table to define it. Working on a project doing this just now. Useful example https://knowledge.safe.com/articles/1046/dynamic-workflow-tutorial-destination-schema-is-de-1.html

source-2-target-schools.fmwt

@mark_1spatial - Heh. Of course. Now that I see what I was doing it seems like such a rookie move The fields are in-line vice segregated side-by-side. I noticed that there was an inspector in your template for unmapped fields. What is the purpose of that object in this context? Regarding the dynamic writer, thanks for that. I believe that will suit this project. I will look into it. Thanks again! -Dave

Just dropped the Inspector in there to trap anything that was unmapped to make sure it was catching everything. Could use a Logger transformer to write to the log file also.