Skip to main content
  • EN

Hello All,

I am trying to aggregate values of rows if specific column values match with another row in the same column. I also want to add that value in that cell which has a higher value. for an example; In the provided screenshot Pararun 111 &112 have duplicated values. They exist in row 8, 9, 25 and 27. I want to sum up Pararun 111 values in row 8 and 112 in row 27 because row 8 and row 27 have bigger value than row 9 and 25.

Sorry, I believe the image was not attached with the question. Thanks

 

 

And, could you post the image?

 

@gsgoraya

Hi,

This workspace creates a table of all attribute and their matches in a excel.

This will give you the row numbers (or a id, I used "run" which is also reflected in the _element_index ).

It is not clear to me what the criterion is for your testing and adding.

You can test within each _attr_name/Value group which attribute in which row satisfied your criterion.

@takashi I have uploaded the image as advised,. Thanks

2017-04-07-15-52-06.pdf

Hi @gsgoraya, this might work if I understand the requirement correctly.

  1. use an Aggregator (or ListBuilder) to aggregate the features grouping by the Pararun Metrics and also generate a list,
  2. sort the list elements descending by the attribute (perhaps the Service Hours?) with a ListSorter,
  3. and then demote the first element of the list as regular attributes with a ListIndexer.

Hi @takashi as advised I have created a workspace but no luck. It does not aggregate values. Please see the screenshot of the workspace.

Hi @takashi as advised I have created a workspace but no luck. It does not aggregate values. Please see the screenshot of the workspace.

In the ListIndexer, you have to specify the index for the first element of the list - i.e. 0 to the List Index parameter.

 

Hi @takashi as advised I have created a workspace but no luck. It does not aggregate values. Please see the screenshot of the workspace.

And you should use the ListSorter to sort the New{}.DATS_ServiceHours by numeric descending, if your relevant attribute was named DATS_ServiecHours.

 

Hi @takashi the worksheet is not adding up the values. For example, in the provided excel screenshot Pararun 109 has duplicated values. The sevicehours of the first value was 96.85 and the second value is 10.98. But the output of worksheet just shows 96.85. I want to add all attributes if Pararun matches.

Hi @takashi

I am sharing workspace with you. Hopefully, it will be helpful to understand the issue. Thanks excel-merge-runs.fmwt

I thought you need to preserve only the row of the largest DATS_ServiceHours value in multiple rows having the same Pararun value. For example, if there were these two rows in the table:

PararunDATS_ServiceHours10996.8510910.98

 

I thought that only the first row should be preserved, because it has higher DATS_ServiceHours value. If I understand the requirement correctly, you should use the ListSorter instead of the Sorter, and then the workflow I suggested should work fine.

However, your actual requirement seems to be different. If you just need to sum the DATS_ServiceHours values in the rows having the same Pararun value, the Aggregator with this setting might help you.
  • Group By: Pararun
  • Attributes to Sum: DATS_ServiceHours

     

The Aggregator generates this row from the two rows above. Is this your desired result?

PararunDATS_ServiecHours109107.83

Hi @takashi

Yes, I want to add the small value to the larger value and also want to preserve a shift type column where it says AM shift, PM shift or mid shift. I am sorry If I could not explain accordingly. As advised, I am now getting the aggregated value but lost the very first column (Shift Type)

Reply


Community Stats

30,129
Posts
114,377
Replies
37,768
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.