Scenarios

Calculate cumulative sum in Informatica

We calculating cumulative sum of salaries on EMP table by DEPTNO as key field.
  • Create source EMP table and design target table with the same source table structure.
  • Drag source to the mapping designer.
  • First sort the data on DEPTNO by using Sorter transformation.
  • Mark DEPTNO as key field and direction is in ascending order. 
  • Create expression transformation and drag ports from sorter to expression. Create below additional ports and write expressions  
            V_SAL(Variable port)=IIF(DEPTNO=V_LAST_DEPTNO, V_SAL+SAL, SAL)
            V_LAST_DEPTNO(Variable port)=DEPTNO
             O_SAL(Output port)=V_SAL  
  • Connect expression transformation to the Target and save the mapping.  
Powered by Blogger.