I am new to Informatica world
My source is like this
emp_id|emp_name|emp_sal
1 | A | 2500
2 | C | 2800
3 | M | 2100
I would like target to be like this..
emp_id|emp_name|emp_sal|maximum_salary
1 | A | 2500 | 2800
2 | C | 2800 | 2800
3 | M | 2100 | 2800
please help guys:) Thanks
You can use an Aggregator transformation. Create an output port in the Aggregator transformation with the expression MAX(emp_sal). Do not select any group by port.
Now join the output of Aggregator and your original source data using a Joiner transformation.
Also you have to use a sorter (key: emp_id) before the Aggregator and select the sorted input option in both the Aggregator and Joiner
Please go through the below steps.
SQ --> Sorter --> Expression -->Tgt
In Sorter t/r, Sort key is emp_sal as descending
Expression for Max Salary in Expression Transformation
v_PORT1(Integer) - v_PORT2 v_PORT2(Integer) -IIF(emp_sal > v_PORT1, emp_sal, v_PORT1) o_PORT - v_PORT2
out put is coming like this.
emp_id|emp_name|emp_sal|maximum_salary 1 | A | 2500 | 2800 2 | C | 2800 | 2800 3 | M | 2100 | 2800
It's working for me.
Please check it and let me know.
According to the performance point of view, we need to use minimum number of transformations and try to reduce the blocking transformations like Joiner, Aggregator.
Here we can achieve this in two transformations.
In Sorter, Sort key is
emp_sal
asdescending
Expression for
Max Salary
in Expression TransformationNote: Datatype for Variable_Input1 & Variable_Input2 is Integer
Hope this will be helpful.