Solved

Find missing number from sequence of row data

  • 28 January 2017
  • 2 replies
  • 6 views
venu
Rank

I have 100 tables.

In each table, there will be 9 row of records stored with unique ID like { 1,2,3,4,5,6,7,8,9 }

Need to log if there is any missing row in those tables with Unique ID.

For eg:

Below the table, there is missing row 4 and 7. need to find 4 and 7 row.

Please advise how to achieve this task.

Thanks in advance

Venu

icon

Best answer by takashi 29 January 2017, 01:11

View original

2 replies

takashi
Rank
Userlevel 2
Badge +17

Hi @venu, if the expected IDs are 1-based sequential numbers, you can generate every ID from 1 to the maximum ID in the table with this procedure.

  1. StatisticsCalculator: Get the maximum ID from the table.
  2. Cloner: Create features for the maximum number. The Cloner adds Copy Number attribute which stores 0-based sequential number to the output features. Here, set the attribute name to "ID". 
  3. ExpressionEvaluator: Add 1 to the number. i.e. change the base of the sequential numbers to 1.

Then, merge the original features and the generated features by the FeatureMerger using the ID as join key. The resulting features from the NotMerged port would contain your desired missing IDs.

0684Q00000ArLbaQAF.png

Alternatively, if you are familiar with Python scripting, a PythonCaller would be a quick solution.

# PythonCaller Script Example
import fmeobjects

class FeatureProcessor(object):
    def __init__(self):
        self.ids = set([])
        
    def input(self, feature):
        self.ids.add(int(feature.getAttribute('ID')))
        
    def close(self):
        for id in sorted(set(range(1, max(self.ids)+1)) - self.ids):
            feature = fmeobjects.FMEFeature()
            feature.setAttribute('ID', id)
            self.pyoutput(feature)
takashi
Rank
Userlevel 2
Badge +17

Hi @venu, if the expected IDs are 1-based sequential numbers, you can generate every ID from 1 to the maximum ID in the table with this procedure.

  1. StatisticsCalculator: Get the maximum ID from the table.
  2. Cloner: Create features for the maximum number. The Cloner adds Copy Number attribute which stores 0-based sequential number to the output features. Here, set the attribute name to "ID". 
  3. ExpressionEvaluator: Add 1 to the number. i.e. change the base of the sequential numbers to 1.

Then, merge the original features and the generated features by the FeatureMerger using the ID as join key. The resulting features from the NotMerged port would contain your desired missing IDs.

0684Q00000ArLbaQAF.png

Alternatively, if you are familiar with Python scripting, a PythonCaller would be a quick solution.

# PythonCaller Script Example
import fmeobjects

class FeatureProcessor(object):
    def __init__(self):
        self.ids = set([])
        
    def input(self, feature):
        self.ids.add(int(feature.getAttribute('ID')))
        
    def close(self):
        for id in sorted(set(range(1, max(self.ids)+1)) - self.ids):
            feature = fmeobjects.FMEFeature()
            feature.setAttribute('ID', id)
            self.pyoutput(feature)
If you need to process all the 100 tables at once and also the possible IDs for every table are always limited to the nine numbers from 1 to 9, this workflow could be a solution. 

 

0684Q00000ArMZ0QAN.png

Reply


Community Stats

28,630
Posts
109,153
Replies
36,507
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.