Informatica: Prev column value display

Chandra Bhushan picture Chandra Bhushan · Dec 30, 2012 · Viewed 8.9k times · Source

The data in the source table is as follows: -

Empno   Ename  sal
101     Allen  1000
102     Alex   2000
103     Tom    1500
104     Cb     2100

I want the output to be as follows :-

Empno   Ename  sal
101     Allen  0
102     Alex   1000
103     tom    2000
104     Cb     1500

Can you please tell me by using which transformation i can acheive the result. Also plz let me know the logic.

Answer

Marek Grzenkowicz picture Marek Grzenkowicz · Dec 31, 2012

Add an expression transformation with two variable ports:

PORT             EXPRESSION
in_sal           N/A
v_previous_sal   v_current_sal
v_current_sal    in_sal
out_sal          v_previous_sal

Read this excerpt from the documentation to understand the port order:

The Integration Service evaluates ports in the following order:

  1. Input ports. The Integration Service evaluates all input ports first since they do not depend on any other ports. Therefore, you can create input ports in any order. Since they do not reference other ports, the Integration Service does not order input ports.

  2. Variable ports. Variable ports can reference input ports and variable ports, but not output ports. Because variable ports can reference input ports, the Integration Service evaluates variable ports after input ports. Likewise, since variables can reference other variables, the display order for variable ports is the same as the order in which the Integration Service evaluates each variable.

    For example, if you calculate the original value of a building and then adjust for depreciation, you might create the original value calculation as a variable port. This variable port needs to appear before the port that adjusts for depreciation.

  3. Output ports. Because output ports can reference input ports and variable ports, the Integration Service evaluates output ports last. The display order for output ports does not matter since output ports cannot reference other output ports. Be sure output ports display at the bottom of the list of ports.