4

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.

enter image description here

Improve this question
4

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:

fme screenshot

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.

Improve this answer
1

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.

enter image description here

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.

Improve this answer
0

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

enter image description here

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.

enter image description here

Improve this answer
3
  • I might be missing something - but how does this detect if a row is missing? – JimT Jan 30 '17 at 14:09
  • Agreed. This one would tell you if an ID existed outside the range 1-9, but you would only know if a row is missing by the number of features that pass the test - but you won't know which row(s). – Mark Ireland Jan 30 '17 at 16:54
  • Right, I think I misunderstood the question. Sorry. – Fezter♦ Jan 30 '17 at 19:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.