Using conditions as parameters in Filter Transformation (Informatica)

This post might be a very basic one. 

I was in a discussion with my colleague and we were translating the requirements to design.

The requirement was to build a flexible functionality in our code to include/exclude records based on a certain value.

 For example, the source (flat file) has the following records –

ID	Value
1	0.3
2	0.5
3	0.6
4	1.0

Based on a business decision at a later stage on the project, the requirement could be any of the following –

Req1: Retrieve all the records that have a Value >= 0.5.

OR

Req2: Retrieve all the records that have a Value > 0.5 only (exclude 0.5 as well)

The condition to check attribute: Value, needed to be parametrized.

Challenge

In the filter transformation, unlike source qualifier transformation, you cannot implement a parameter to include the operator.

In other words, you cannot have a parameter -

$$Condition = >= 0.5

and put the following code in the filter transformation –

Value $$Condition

You will get an error:

<< PM Parse Error >> missing operator

…

Value >>>> <<<< $$Condition

Solution

The workaround is to use two parameters.

$$Condition1 (Datatype = double)

$$Condition2 (Datatype = double)

In the filter transformation, write the following code –

Value = $$Condition1 OR Value > $$Condition2

So, if the decision is to go with Req1 (Retrieve all the records that have a Value >= 0.5.), then in the parameter, assign:

$$Condition1 = 0.5

$$Condition2 = 0.5

The resultant condition in the filter transformation would be:

Value = 0.5 OR Value > 0.5

The output will be –

ID	Value
2	0.5
3	0.6
4	1.0

If the decision is to go with Req2 (Retrieve all the records that have a Value > 0.5 only), then in the parameter, assign:

$$Condition1 = -999 (or any non-existent value)

$$Condition2 = 0.5

The resultant condition in the filter transformation would be –

Value = -999 OR Value > 0.5

The output will be –

ID	Value
3	0.6
4	1.0