1

I have a one-to-many joined table (i.e. duplicated AssetIDs with other different attributes), and I would like to consolidate the table (20 records) into one single record.

However, when using Summarize it takes the max compare score (3), but it always takes the lowest near_dist (0.015821 instead of 0.215665)

I have tried the summary statistics tool and dissolve, but cannot get the 2 conditions correct.

  • 1
    Summarize should work. You probably need to export the joined table to a new table before summarizing. Sometimes joins have strange behavior in arcgis – jbalk Jan 3 at 19:00
  • @jbalk I already exported to a new table. However, when using Summarize it takes the max compare score (3), but it always takes the lowest near_dist (0.015821 instead of 0.215665) – LearningGIS101 Jan 3 at 19:20
  • 2
    You should edit your question to clarify that. You're asking to find the max of the compare score and min of the near_dist and that is 3 and 0.015821. If you want the max compare score, then the min of the near_dist with max compare score, that's asking something different that your question is currently asking. You'll need a multi step process - first find max compare score then filter based on that score, then find min near_dist from the filtered records and store it in another table along with the leaks_join_field, then dissolve your records based on the leaks_join_field. – jbalk Jan 3 at 19:39
  • 1
    Then join your second table with the min near_dist back to the output based on the leaks_join_field. – jbalk Jan 3 at 19:39
  • 2
    You could also use a search cursor if you're familiar with arcpy – jbalk Jan 3 at 19:40
1
  1. Dissolve your table on Leaks_join_field and keep for last step
  2. Summarize your initial table on Leaks_join_field, keeping max of COMPARE_SCORE
  3. Join the summarize result back to the initial table on Leaks_join_field
  4. Add a definition query to the effect of 'main_table.COMPARE_SCORE = sum_table.COMPARE_SCORE'
  5. Summarize the filtered table on Leaks_join_field, keeping min of MAINS_NEAR_DIST
  6. Join the output of the second summarize back to the initial table on Leaks_join_field
  7. Change the definition query to 'main_table.COMPARE_SCORE = sum_table.COMPARE_SCORE AND main_table.MAINS_NEAR_DIST = sum_table_2.MAINS_NEAR_DIST' - now you're left with your initial table filtered down to the records you want.
  8. Export the filtered table to a new table.
  9. Use the 'Join Field' tool to join the COMPARE_SCORE and MAINS_NEAR_DISTANCE fields from the filtered table to the dissolved table from the first step. Now you have one record for each Leaks_join_field with its max compare score and min near distance per max compare score.

If your table does not have geometry, skip step 1 and step 9

| improve this answer | |
  • What if the table does not have a Geometry field? – fatih_dur Jan 4 at 0:34
  • @fatih_dur Then you don't need to first or last step – jbalk Jan 4 at 0:34

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.