I have 40 tables with about 2000 row with a shared ID that I want to merge into 1 table with 2000 rows. I only want to retain a distance field calculated within each table. The distance field is uniquely named Dist_miles_[name] with "[name]" as a unique value for each table. This way in the final table I will have: Key value, Dist_miles_[nameofAdvisor1], Dist_miles_[nameofAdvisor2], Dist_miles_[nameofAdvisor3]... Dist_miles_[nameofAdvisor40].
I would like to use iterate table and join field, and delete field to remove all the extraneous fields. However, the specificity of the variable I want to keep poses problems because I can't select it in the join field tool. I was hoping the join field tool would take a wildcard and be able to pull only the Dist_miles* into the final output table, but it doesn't.
Is there a workaround this?
Used Polygeo's tip to create modelbuilder. Works fine for single table. Tried to iterate on tables but doing that created an error at Calculate tool: "A field name was not found or there were unbalanced quotation marks. ERROR 999999: Error executing function. Failed to execute (Calculate Field)."
Answer
So problem solved. This post encountered a similar issue. It's possible to ignore the field selection in the join field tool. I was not able to use add join and run the iterators successfully. http://forums.arcgis.com/threads/73410-Model-Builder-Using-iterator-and-Add-Join-tools-to-join-tables
The starting materials for this process is a spreadsheet of addresses for members in a program, and a spreadsheet of addresses for advisors in a program. I wanted to end with a spreadsheet of member's addresses with columns containing the distance of that member to each advisor.
1) Import and geocode the member and advisor spreadsheets.
2) Using a split layer by attribute tool, I created individual shapefiles for each advisor. The tool saved the advisor's name (the key field) into the output file by default. I chose to name the files Dadv_[advisornamehere]
3) The individual advisors were fed iteratively into a generate near table tool with the member addresses. The tool added a column in the output near table to house the distance in a variable named for that advisor (ex: Dist_miles_%Name%) which pulled in the name of the individual advisor table. The column was then filled with the distance generated in the near table generically named Near_Dist using the calculate field tool.
The second iterator feeds the generate near tables into an iterative join with the member table. 
I still need a way to delete all the excess fields that were joined, ex: Near_Dist. So any tips on that would be great. For now I'll be manually deleting in the export to excel.
No comments:
Post a Comment