top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration

Facebook Login
Site Registration

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

0 votes
62 views

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

posted Sep 8, 2014 by Amit Sharma

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

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

answer Sep 10, 2014 by Shweta Singh
Similar Questions
0 votes

I have Informatica 9 and an Oracle database in my system. Now I want to install a SQL Server database in my system and add this database to Informatica.

Is that possible ?

Purpose: I need to migrate some of the tables from SQL Server to Oracle database using informatica.

Could anyone let me know, after installing SQL Server, how can I add this SQL Server database to informatica for creating mapping?

+1 vote

My source having 10 records but how can i load 20 records in target, i am not bother about duplicates. How I can do it?

+2 votes

In my mapping it tooks long time to fetch rows to the target table but it reads those rows from .CSV file within a minute.In this mapping One source is a flat file another source is a table which is having data from target.Here we create a logic to UPDATE or DELETE or INSERT in target based on comparison on the data from flat file and the source table(data from target table).while seeing sesion log it reads data from flat file within a minute but it fetches those data to the target to taget 9 rows/sec throu enter image description hereghput.Here the target table is created using unix script ie CREATE TABLE STG_LM_INSTITUTION as (SELECT * FROM LM_INSTITUTION);Target in this mapping is STG_LM_INSTITION.this is the copy of final target(LM_INSTITUTION).I think the problem is due to creation of the taget table in script but i'm not sure.Anyone please help me to solve this issue.Source flat file has 2L rows. I run this mapping with 2L rows.After 11hrs it fetches only 1L records into the target.But while running using 500rws only it fetches records in one minute to the target .

Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...