Solved

Testing on a group of rows

  • 25 August 2017
  • 7 replies
  • 39 views
C
Badge

Hi everyone!

I'm new to FME (using 2016) and have a question that keeps bothering me.

I have an excel sheet with inspection results from a different company, these are based on different parts of an object. Each row is a different part of the object, with a column with specific inspection results. If any of the parts of the object has a value in the inspection column, I need the entire object to be copied to an output sheet. If none of the parts of an object has a value, the entire object needs to go to a different output sheet.

The input looks like this (simplified).

And the result should look like this:

The objects with any row that contains a value in InspectionResult

The objects with without a row that contains a value in InspectionResult

It seems so easy, it looks like testing on a group but I can't figure out a solution. I tried selecting (tester) only the rows with a value, make a list of unique value of objectnumbers (list builder) and merge them again to the original table (FeatureMerger). Tried a loop in a custom transformer to.

Any help would be great.

Thanks!

Casper

icon

Best answer by mark2atsafe 25 August 2017, 19:39

View original

7 replies

erik_jan
Rank
Userlevel 2
Badge +16

Have a look at the Tester transformer.

It can test if a selected attribute has a value.

If yes it will output through the Passed port else through the Failed port.

The test can be combined for multiple attribues using the AND boolean logic.

danilo_fme
Rank
Userlevel 4
Badge +30

Hi @casper,

I simulated here using a tester to filter Inspection contains X.

After i used a FeatureMerger and this transformer generate a attribute called numReferences.

Again i used a FeatureMerger to compare by Objectnumber:

Attached the workspace template.

Thanks, - workspace-excel.fmwt

Danilo

erik_jan
Rank
Userlevel 2
Badge +16

Another option would be using the StatisticsCalculator after testing with the Tester for values. If you use the Count in the statistics combined with the Group by on attribute Objectnumber, you get per Objectnumber the number of records with a value for InspectionResult.

takashi
Rank
Userlevel 2
Badge +17

Hi @casper,

I simulated here using a tester to filter Inspection contains X.

After i used a FeatureMerger and this transformer generate a attribute called numReferences.

Again i used a FeatureMerger to compare by Objectnumber:

Attached the workspace template.

Thanks, - workspace-excel.fmwt

Danilo

similar approach, but could be simpler.

 

 

mark2atsafe
Rank
Userlevel 4
Badge +25

I would:

- use an Aggregator (group-by Objectnumber), with InspectionResult as the Attribute to Concatenate

- use a Tester to test where the concatenated result has a value (or not)

- use a Deaggregator (one for Passed, one for Failed) to flatten the data back again

L
Badge

Another option would be using the StatisticsCalculator after testing with the Tester for values. If you use the Count in the statistics combined with the Group by on attribute Objectnumber, you get per Objectnumber the number of records with a value for InspectionResult.

Maybe:

 

  • StatisticsCalculator with Group By Objectnumber to calculate InspectionResult max for each object;
  • all features can be output through Complete port with max(InspectionResult) added as an attribute;
  • with Tester, features with empty max(InspectionResult) can be separated from features with max(InspectionResult) that has a value - these will be objects with no InspectionResult for any of the parts vs. objects with at least one part that has InspectionResult.

 

C
Badge

Thanks everyone for your reply!

I used Takashi's solution because it was similiar of what I was trying to do. Only difference was that I used Geometry Only as Feature Merge Type, my Excel doesn't have geometry so it won't merge, as I intended. That worked like a charm.

When I have more time I will try the other solutions.

Thanks again!

Reply


Community Stats

28,714
Posts
109,490
Replies
36,560
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.