Dissolving while retaining field informations in the same entry instead of multiple entries

Question

I have 1520 covenants. Many covenants have more than 1 VM symbols attached to it, currently showing as rows. EG. Covenant #97 has 4 VM Symbols, with the VM Symbols 1, 67, 117 and 171. How do I change the table to show Covenant #97 as a single entry with 4 columns, each column showing 1, 67, 117 and 171. There will be entries with more or less columns populated. This is fine as those columns will be left as null. There will be a maximum of 8 VM Symbol Columns as 8 is the maximum frequency which occurs for a covenant. IS there a quick way of doing this? Otherwise, a model or a python script would also be welcome.

I thought if there is a way to manually add new fields and populate the field with the relevant symbols, I can do a dissolve at the end for FID Covenants so we don't end up with multiple similar entries.

Answer 1

the quick way to do this is to use the dissolve tool and join the result of the pivot table tool to the output. Unfortunately the latter tools needs an advanced licence. Otherwise most most pragmatic approach with "only" eight possible fields is to add the fields manually, fill them with values based on the selection and dissolve with the sum of the fields.

Answer 2

If I were going to do this without scripting, I would do it in several steps.

1. Make a copy of the table as your working table, if you need to save it as it is.
2. First, I would use the Delete Identical tool, using VM_Symbol and FID_Covenants as the defining fields.
3. Next, add a long text column called CONCATENATED and populate it with the field calculator to contain a comma-delimited list of the VM_Symbol values for each FID_Covenant value. You'll end up with duplicate values in this new column.
4. Use Delete Identical again to delete the identical CONCATENATED values.
5. Manually add your 8 columns.
6. Use the field calculator on each of the 8 columns to populate it from the CONCATENATED column. Parse it out so Item 1 goes to the first column, Item 2 goes to the second column, etc. You'll have account for NULLs. I would put the CONCATENATED value into a python list and check the length (count of list items). For example, if there is no Item 3, don't try to populate Column 3.

If this is a one-time thing, building a model probably wouldn't be any faster than just doing it.

If it is not a one time thing, and you want to script it, you probably want to make use of data access cursors and dictionaries. That would probably be another question though.

Let us know if you need more detail. The ESRI examples on how to do python code blocks with the field calculator are helpful.

Answer 3

Cool guys thanks !

I just found that the Pivot Table tool does this for me pretty easily and then I can Dissolve usually FID_Covenants for duplicate records. How cool is that !