Skip to main content
  • EN

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

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)

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

30,126
Posts
114,372
Replies
37,764
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.