Informatica filter transformation just filters the records.
There is an alternative to filter. That is source qualifier. Source qualifier can also filters the records. But there is a constraint that it only works for relational sources. It can’t filter the records in Flat Files.
This is an attempt to write simple explanation for informatica filter which can be consumed easily. I hope it will be concise and simple with few scenarios.
BASIC DEFINITION OF INFORMATICA FILTER: There is no formal definition for filter. It is one another Informatica transformation which can filter the records. Apology if it looks stupid. 😉
Sample SCENARIOS TO UNDERSTAND INFORMATICA FILTER
What will be output for the following two different filter conditions in different filter (NAME = XYZXX) and (NAME = xyzxx. 10) respectively. Source table has 10 records with value ‘XYZXX’ and 5 records with value ‘xyzxx’.
Answer 1: For first filter condition output will be 10 rows in the first flow of mapping. For second filter condition output will be 5 rows for second flow of mapping. This is just to ensure your understanding on filter conditions that they are case sensitive.
You cannot concatenate ports from more than one transformation into the one Filter transformation. The input ports for the filter must come from a single transformation.
Answer 2: No clue on this., from my understanding if the input channels are from passive transformations and data initialized from a single channel, then it should allow in filter transformation.
You have 10 million records in which 2 million are having null values and 1 million are with spaces in a Flat File. How will you design a mapping to filter only the records with spaces not with NULL values?
Answer 3: Put a filter to remove the spaces. You cannot remove it with Source qualifier filter condition because it is Flat File. There is function as IS_SPACES in informatica to identify if the row has only spaces (to remove space value). Main idea of this question is to check your awareness about this function.
So filter condition will be “IS_SPACES(COLUMN_NAME) != 1”. PN: This will work only for string datatype.
I have data from two countries say example US and INDIA. My filter condition is COUNTERY = US. What will be the best way to filter out data in a mapping?
Answer 4: Source Qualifier transformation only lets you filter rows from relational sources, while the Filter transformation allows you to filter the rows from any type of source. So if it is Flat File we need to use Filter otherwise for relational Database Source Qualifier will be enough.
Using filter transformation (with condition_country=US) next to SQ transformation.
What are the alternatives of filter transformation?
Answer 5: Even i am not sure about it , Will update this part later.
Can we place filter after update strategy? Will mapping be valid?
Answer: Yes we can place filter after update strategy. Mapping will be valid.
Is there any scenario you can describe where we need to place filter after update strategy?
Can we parameterize the filter condition, if yes please let me know the example?
Answer: Yes., Consider if you have a midstream filter transformation with filter condition (fiscal_year > 2013 AND fiscal_year < 2014) to filter old data in mapping. The values for these years change over a period of time. you can parameterize year values and write a filter condition in the below manner and pass the fiscal year values as parameters.
Fiscal_year > $$FISCYR1 AND fiscal_year < $$FISCYR2
Can we write or call stored procedure or user defined function in filter?
Answer: Need to check !!
I have created a mapping and I am middle of unit testing. My Source has 1 Petabyte of data and I only want to test first few records to ensure that mapping is running good. How can I do it?
Answer: You can do it by placing filter after update strategy. Moreover in sessions Need to check Help (will check later)
Performance Factors While Designing Mappings With Informatica Filter
As we are aware that with Source Qualifier transformation also we can filter rows. If possible use SQ rather filtering rows from within a mapping, It is advised to keep the Filter transformation as close as possible.