Create a Logical data object.
In the mapping of the logical object source from your source table.
Join the source to the .txt file using a a joiner on the fields you want to confirm are in the file.
After the joiner put a filter where you filter all records that have a null value from the .txt file. This will remove any rows from the source that do not have a value in the .txt.
The output goes to the logical table.
You can profile that logical table which executes the mapping behind the scenes to perform the filter.
very high level but that is one way you can do it.
Thanks Scott for this solution. I am new to Informatica Developer. When you say, the output goes to logical table, it will update the database table?
You create an temp table in the database with the list of values you wan to filter. Then, in the profile source / data object, you can write a query something like below:-
Select * from table1 where ID NOT IN (select ID from temptable)
This way the results fetched from the 'table1' are filtered at the database level and there is no additional filter that needs to be added at the profile object level.
Could you please provide detailed steps?
sunilsa I may not able to alter the database in any way. Also, this list is dynamic, we will have one more table to update other than the .txt everytime we add anything to the list. Is it possible to use the reference tables in DQ ?
No it will not update the database. The logical table is held in informatica memory that allows you to profile.