2

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)."

|improve this question
  • I would try 40xAddField/AddJoin/CalculateField plus will need MakeTableView to start. – PolyGeo♦ May 30 '13 at 21:59
  • thanks polygeo. so that did accomplish the join but is there a way to make this more generic so that I can reuse the tool? I'm creating a process with a test set of Advisors, but the list of Advisors will change. I will also have to repeat this process multiple times over the course of the summer which would be very tedious. That's why a wildcard would have been helpful. – ajeedee May 30 '13 at 22:14
  • So I figured out that I can use parse path and apply the name of the table to some of the parameters. That's one step closer. – ajeedee May 30 '13 at 22:34
  • Make sure you take a look at the Model Only tool called Collect Values – PolyGeo♦ May 30 '13 at 23:09
  • I'm not sure I follow how that fits in. – ajeedee May 31 '13 at 0:02
3

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.

enter image description here

The second iterator feeds the generate near tables into an iterative join with the member table. enter image description here

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.

|improve this answer
0

You can delete all the excess fields using the 'delete field' tool. Within this tool you can easily select all the fields that you don't want to have, even if the table still needs to build through ModelBuilder.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.