What is rolling sum and how to impliment it in Informatica and my requirement is as follows?

Question

Can someone pl tell me what is rolling sum and how to implement it in Informatica?

My requirement is as below:(Given by client)

ETI_DUR : SUM(CASE WHEN AGENT_EXPNCD_DIM.EXCEPTION_CD='SYS/BLDG ISSUES ETI' THEN IEX_AGENT_DEXPN.SCD_DURATION ELSE 0 END)

ETI_30_DAY : ROLLING SUM(CASE WHEN (SYSDATE-IEX_AGENT_DEXPN.ROW_DT)<=30 AND AGENT_EXPNCD_DIM.EXCEPTION_CD = 'SYS/BLDG ISSUES ETI' THEN IEX_AGENT_DEXPN.SCD_DURATION ELSE 0 END)

ETI_30_DAY_OVRG : CASE WHEN ETI_DUR > 0 THEN CASe WHEN ROLLINGSUM(ETI_DUR_30_DAY FOR LAST 29 DAYS) BETWEEN 0 AND 600 AND ROLLINGSUM(ETI_DUR_30_DAY FOR LAST 29 DAYS) + ETI_DUR > 600 THEN ROLLINGSUM(ETI_DUR_30_DAY FOR LAST 30 DAYS) - 600 WHEN ROLLINGSUM(ETI_DUR_30_DAY FOR LAST 29 DAYS) > 600 THEN ETI_DUR ELSE 0 END ELSE 0 END

And i have implemented as below in Informatica.

Expression Transformation:

o_ETI_DUR-- IIF(UPPER(EXCEPTION_CD_AGENT_EXPNDIM)='SYS/BLDG ISSUES ETI',SCD_DURATION,0)

o_ETI_29_DAY-- IIF(DATE_DIFF(TRUNC(SYSDATE),trunc(SCHD_DATE),'DD') <=29 AND UPPER(EXCEPTION_CD_AGENT_EXPNDIM) = 'SYS/BLDG ISSUES ETI' ,SCD_DURATION,0)

o_ETI_30_DAY -- IIF(DATE_DIFF(TRUNC(SYSDATE),trunc(SCHD_DATE),'DD') <=30 AND UPPER(EXCEPTION_CD_AGENT_EXPNDIM) = 'SYS/BLDG ISSUES ETI' ,SCD_DURATION,0)

Aggregator transformation:

o_ETI_30_DAY_OVRG: IIF(sum(i_ETI_DUR) > 0, IIF((sum(i_ETI_29_DAY)>=0 and sum(i_ETI_29_DAY)<=600) and (sum(i_ETI_29_DAY)+sum(i_ETI_DUR)) > 600, sum(i_ETI_30_DAY) - 600, IIF(sum(i_ETI_29_DAY)>600,sum(i_ETI_DUR),0)),0)

But is not working. Pl help ASAP.

Thanks a lot....!

Answer 1

Here is a rolling sum example:
count, rolling_sum
1,1
2,3
5,8
1,9
1,10

Basically it is the sum of the values listed previously. To implement it in Informatica use 'local variables' (variable port in expression transformation) as follows:

input port: count
variable port: v_sum_count = v_sum_count + count
output port: rolling_sum = v_sum_count