topic Re: Cannot perform field calculations with joined excel data in ArcGIS 10? in Data Management Questions

Cannot perform field calculations with joined excel data in ArcGIS 10?

ScottFretwell — Sun, 29 Apr 2012 06:35:03 GMT

I joined an excel table to the table of a shape file, but I can not perform field calculations on the excel data that was joined, but can still perform calculations on the original data from the shape file. Any thoughts?

Re: Cannot perform field calculations with joined excel data in ArcGIS 10?

TimothyHales — Tue, 01 May 2012 13:04:49 GMT

When calculating joined data, you cannot calculate the joined columns directly. However, you can directly calculate the columns of the origin table. To calculate the joined data, you must first add the joined tables or layers to ArcMap. You can then perform calculations on this data separately. These changes will be reflected in the joined columns.

Calculate Field (Data Management)

Re: Cannot perform field calculations with joined excel data in ArcGIS 10?

KaelDowdy — Mon, 01 Jul 2013 15:04:56 GMT

When calculating joined data, you cannot calculate the joined columns directly. However, you can directly calculate the columns of the origin table. To calculate the joined data, you must first add the joined tables or layers to ArcMap. You can then perform calculations on this data separately. These changes will be reflected in the joined columns.

Calculate Field (Data Management)

I know this is an older post, but I'm running into a similar problem...

I'm wanting to run the Calculate Field too on a combination of fields from both the table where the join table will be joined to and the join table itself. For example, I have a layer of Pipelines (the layer to which the join table will be joined) and a layer called Pipeline_Stats (the table to be joined to the input layer (Pipelines)). But when I run the Calculate Field tool, and choose the Pipelines layer as the Input Table, I only see fields originally contained in the Pipeline layer, and none of the fields from the joined Pipeline_Stats table. I could copy the data over I suppose, but I'm not sure which tool to use to accomplish this. Plus, that somewhat defeats the purpose of the Join, I guess.

This will be run inside a Model, so I can automate the process if it'll take multiple steps to get it accomplshed -- although the simpler/easier would be best...

Thoughts?

Re: Cannot perform field calculations with joined excel data in ArcGIS 10?

RichardFairhurst — Mon, 01 Jul 2013 15:26:04 GMT

I know this is an older post, but I'm running into a similar problem...

I'm wanting to run the Calculate Field too on a combination of fields from both the table where the join table will be joined to and the join table itself. For example, I have a layer of Pipelines (the layer to which the join table will be joined) and a layer called Pipeline_Stats (the table to be joined to the input layer (Pipelines)). But when I run the Calculate Field tool, and choose the Pipelines layer as the Input Table, I only see fields originally contained in the Pipeline layer, and none of the fields from the joined Pipeline_Stats table. I could copy the data over I suppose, but I'm not sure which tool to use to accomplish this. Plus, that somewhat defeats the purpose of the Join, I guess.

This will be run inside a Model, so I can automate the process if it'll take multiple steps to get it accomplshed -- although the simpler/easier would be best...

Thoughts?

The prior response is in error in implying you can use the field calculator on the joined table itself. Only the primary layer/table can be altered using the Field Calculator, not the table joined to it. Besides, you cannot derive statistical summary data using the field calculator across multiple records.

You could transfer the joined summary data into a field in the Pipelines themselves using the join you have set up and the field calculator. But you probably don't need to do that and are already getting what you need by doing other calculations based on those joined values without transferring the values themselves.

To calculate values into the Pipeline_Stats, you would need to reverse the join so that Pipeline_Stats is the primary table. However, I assume more than one Pipeline relates to a single stat row. In that case first use the Summary Statistics tool against the Pipelines to summarize down to values on one record per the join field value of Pipeline_Stats. To do this keep the field that Pipelines shares with the Pipeline_Summary as the unique case field (a separate field list from the summary field list). Then perform summaries on the other fields, like counts, sums, min, max, mean, std dev, first, last, etc. Join the created summary output to the Pipeline_Summary table and update its fields based on the new summary of the Pipelines using the Field Calculator. If the Summary_Statistice tool does what the Pipeline_Stats calculation would do, you could consider just replacing Pipeline_Stats by rerunning the Summary_Statistics tool model as needed.