• EN
Solved

How do I map values from a source to a target table using a crosswalk table

D
Badge

Hi -

I am attempting to translate and map attribute values from a source table to a target table. The image above is an example of what I'm trying to accomplish (not actual data.) I have created a crosswalk table using the frequency GP tool in ArcGIS that maps the original values to the the desired translations to be stored in the target table. I assume the processor to use is the attribute value mapper but I haven't had much luck. I'm new to FME so could be "pilot error." Thanks.

icon

Best answer by mark_f 6 March 2016, 22:21

View original

10 replies

M
Badge +2

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.

M
Badge +2

@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)

 

Set New Attribute (new attribute with a new value - could be the same attribute by the way)

D
Badge

@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)

 

Set New Attribute (new attribute with a new value - could be the same attribute by the way)

@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

M
Badge +2

@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)

 

Set New Attribute (new attribute with a new value - could be the same attribute by the way)

@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.

D
Badge

@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)

 

Set New Attribute (new attribute with a new value - could be the same attribute by the way)

@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

mark2atsafe
Rank
Userlevel 4
Badge +25

@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

D
Badge

@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)

 

Set New Attribute (new attribute with a new value - could be the same attribute by the way)

@mark_1spatial

- 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

M
Badge +2
@mark_1spatial

- 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

 

D
Badge

@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

M
Badge +2

@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.

Reply


Community Stats

28,700
Posts
109,458
Replies
36,550
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.