How to get max of a column in informatica?

问题:

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

回答1:

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

SQ --> Sorter --> Aggregator -- Joiner --> Target
         |_________________________^

回答2:

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.

 SQ --> Sorter --> Expression --> Output

In Sorter, Sort key is emp_sal as descending

Expression for Max Salary in Expression Transformation

Variable_Input1 - Variable_Input2
Input - emp_sal
Variable_Input2 - (IIF(Input > Variable_Input1, Input, Variable_Input1))
Output - Variable_Input2

Note: Datatype for Variable_Input1 & Variable_Input2 is Integer

Hope this will be helpful.

回答3:

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.