Handling Attributes

[BrendanQuigley]

Hello

I have a simple question that is hard to explain. I have drawn a little picture to help explain.

[ATTACH=CONFIG]26206[/ATTACH]
https://www.dropbox.com/s/t0ncq91l6wgl3ja/IMG_20130725_104657.jpg

Basically I have table 1 and I want to get to table 2 based on the values in an attribute field. I know that there is a maximum of 12 unique values for any one D value. This means at max I would have 12 new fields (which can be named 1 - 12).

I have 1000's of D values in total.

Any advice on where to start, would be helpful. I have tried an intersect and join but found they only returned one value for each D.

Thanks

[RichardFairhurst · Accepted solution]

Hello

I have a simple question that is hard to explain. I have drawn a little picture to help explain.

[ATTACH=CONFIG]26206[/ATTACH]
https://www.dropbox.com/s/t0ncq91l6wgl3ja/IMG_20130725_104657.jpg

Basically I have table 1 and I want to get to table 2 based on the values in an attribute field. I know that there is a maximum of 12 unique values for any one D value. This means at max I would have 12 new fields (which can be named 1 - 12).

I have 1000's of D values in total.

Any advice on where to start, would be helpful. I have tried an intersect and join but found they only returned one value for each D.

Thanks

This is what the pivot table does, although you would need an Advanced license to use that tool.  For the unique value field if you are sure it has no duplicates you can use it as both the pivot field and the value field.  The result would be that every record with 6 in the unique field would be in the UNIQUE_6 column and have a value of 6 or null.  However, all columns numbered UNIQUE_1 through UNIQUE5 and UNIQUE_7 thru UNIQUE_12 wouldl have null values if 6 was the only value associated with the D value.

If you wanted the value of 6 to be in the second column when there are only two values for a given D value, then you need to Sort the table and create a new numeric field to calculate a unique number indicating the column position of each value.  This requires Sort/Summary, add numeric column, calculate it to equal ObjectID, Summarize on D value and get the min of the new column, Join, calculate the new column to equal:

Sort_table.New_Column - Join_Table.Min_New_Column + 1

that will result in each value being in order with all null column being in positions after the filled in columns.

[BrendanQuigley]

This is what the pivot table does, although you would need an Advanced license to use that tool.  For the unique value field if you are sure it has no duplicates you can use it as both the pivot field and the value field.  The result would be that every record with 6 in the unique field would be in the UNIQUE_6 column and have a value of 6 or null.  However, all columns numbered UNIQUE_1 through UNIQUE5 and UNIQUE_7 thru UNIQUE_12 wouldl have null values if 6 was the only value associated with the D value.

If you wanted the value of 6 to be in the second column when there are only two values for a given D value, then you need to Sort the table and create a new numeric field to calculate a unique number indicating the column position of each value.  This requires Sort/Summary, add numeric column, calculate it to equal ObjectID, Summarize on D value and get the min of the new column, Join, calculate the new column to equal:

Sort_table.New_Column - Join_Table.Min_New_Column + 1

that will result in each value being in order with all null column being in positions after the filled in columns.

Wow, this looks like it is working. I haven't tackled the sort part yet. With the simple PivotTable_management I am getting more rows than I require.

Every D with one year is being put in the table, then the last D with have the two years that is suppose to go with it. Can I somehow eliminate the duplicates that only have the single data?

Thanks

[RichardFairhurst]

Wow, this looks like it is working. I haven't tackled the sort part yet. With the simple PivotTable_management I am getting more rows than I require.

Every D with one year is being put in the table, then the last D with have the two years that is suppose to go with it. Can I somehow eliminate the duplicates that only have the single data?

Thanks

Not sure I understand your last sentence.  Sounds like you should do a Summary Statistics instead of Sort in the steps I mentioned.  You could then select based on a count value if that applies.  Do the pivot on that summary.  If that does not help, please explain further what you mean.