Find missing number from sequence of row data in FME

Question

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.

Answer 1

Create a reference table containing all 9 rows. Read this table into your workspace and route into a joiner. In the joiner specify the input table to be tested and set cardinality to 'Must match zero or one'. Then the _matched_records attribute will contain number of records matched; use a tester to check for when _matched_records==0 and then route it into whatever action you need. Something like this:

If you have a large number of tables, then you could use a script to iterate over all the files or use the workspace runner.

Answer 2

I'm not sure I like the idea of creating a separate table just for this; especially since you know the numbers will be 1-9.

You could do the same thing fully in FME by using a Creator to create 9 features and testing with a FeatureMerger instead of a Joiner.

Or - in a variation on the @Fezter answer - you could use an AttributeFilter to split the data and then a NoFeatureTester to test which is missing.

I don't say it's particularly elegant, but it would work, and the Empty/Missing/Null/Unfiltered ports add an element of QA if the ID is something completely different.

Answer 3

Another way you can achieve this is simply by using a Tester transformer. It means you don't need to store anything in another table.

I created 100 features and assigned them random integer values from 1 to 11. I connected a tester transformer and set the test to

ID In 1,2,3,4,5,6,7,8,9

Anything that is in those values will go to the "Passed" output. Anything else will go to "Failed".

Just like JimT said, you could use a workspace runner. But I prefer just using a dynamic reader to read all of your CSV files at once.