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.
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:
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.
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.
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.