I have a table of non-spatial features. Each row has a non-primary key that it shares with 39 other rows. Each row contains a unique attribute type. For example: Row 1= "10 County"; Row 2 = "10 State"; Row 3 = "10 District" .... Row 41 = "20 County", Row 42 = "20 State" .... etc. How can I pull all the "10" attributes "20" attributes into single rows, each containing the 40 attributes?
Question
Aggregating Multiple Rows
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
+62
I'm having a hard time getting my head around this issue, but if I read you correctly an AttributeSplitter or SubstringExtractor to grab the first two characters from the value and store them in a new attribute, then a ListBuilder set to group by that attribute and a ListConcatenator to turn them into a single line would do the trick.
Thanks for the quick feedback and my apologies for the late response.
I believe the ListBuilder will get me to where I need to be, but in a roundabout way. I'm hoping a simpler technique is available. Let me restate the problem (hopefully) more clearly, now that I have more insight:
- My input table contains sets of rows grouped by a common GROUP_ID.
- Each row contains a single FIELD_ID and FIELD_VALUE
- FIELD_ID is the lookup key to a FIELD_NAME. I am using AttributeValueMapper to assign each FIELD_ID its FIELD_NAME. For example: FIELD_ID = 0004001, which translates to FIELD_NAME = Latitude
- Output from AttributeValueMapper is a set of rows containing GROUP_ID, FIELD_NAME, and FIELD_VALUE as in the following example:
- 25 Latitude 39.144679
- 25 Longitude -81.655892
- 25 StructLen 483
- 31 Latitude 38.422415
- 31 Longitude -81.972522
- 31 StructLen 201
Feeding the above into ListBuilder I get output similar to the following:
GROUP_ID{0}.FIELD_NAME{0}.FIELD_VALUE{1}.FIELD_NAME{1}.FIELD_VALUE{2}.FIELD_NAME{2}.FIELD_VALUE25Latitude
39.144679Longitude-81.655892StructLen48331Latitude38.422415Longitude-81.972522StructLen201How can I create a table/feature where FIELD_NAMEs are columns containing the FIELD_VALUEs such as follows:
GROUP_IDLatitudeLongitudeStructLen2539.144679-81.65589248331
38.422415-81.972522201This would then let me use Tester to filter out StructLen <= 100
My apologies if this turns out to have a simple answer. I'm still very much a newbie.
+40- Contributor
If you have a feature with 3 attributes
Group_ID: 25
Field_ID: Latitude
Field_Value: 39.144679
You can use an attributeCreator with the New Attribute set to @Value(Field_ID) and the Attribute Value set to @Value(Field_Value).
Remove your Field_ID and Field_Value attributes and Aggregate grouping by Group_ID and accumulation mode set to Merge Incoming Attributes
If you wish to use those attributes in the workspace, you will need to explicitly expose them (AttributeExposer)
+22- Safer
If you have a feature with 3 attributes
Group_ID: 25
Field_ID: Latitude
Field_Value: 39.144679
You can use an attributeCreator with the New Attribute set to @Value(Field_ID) and the Attribute Value set to @Value(Field_Value).
Remove your Field_ID and Field_Value attributes and Aggregate grouping by Group_ID and accumulation mode set to Merge Incoming Attributes
If you wish to use those attributes in the workspace, you will need to explicitly expose them (AttributeExposer)
+40- Contributor
I think that @jdh hit the nail on the head here. I will add to this by suggesting using a SchemaSetter before the writer to automagically build the schema which can then be used on the writer, this lets you avoid having to expose them manually. You may need to use a BulkAttributeRemover to remove any format specific hidden attributes (xlsx* or multi*) before you do this though.
PS. I love that you used the term automagically.
Hi @randall_robie, an excellent solution has been suggested by @jdh, but there are other possible approaches too.
A: If you have aggregated the features with a ListBuilder already, Python scripting may be a quick way to create desired attributes. You can expose required attribute names in the PythonCaller parameters dialog (see the Attributes to Expose parameter).
# PythonCaller Script Example
def processFeature(feature):
names = feature.getAttribute('_list{}.FIELD_NAME')
values = feature.getAttribute('_list{}.FIELD_VALUE')
for name, value in zip(names, values):
feature.setAttribute(name, value)
B: Alternatively, a combination of BulkAttributeRenamer and Aggregator works fine too. This method renames 'FIELD_VALUE' to the value of 'FIELD_NAME' directly, so won't create any excess attributes.
Just be aware that you have to expose the attribute names if the attributes should be referred in the subsequent workflow.