top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

how to implement row level summmary function in informatica

0 votes
522 views

Can someone please explain me how to implement the following logic in informatica. but Not with source qualifier with other transformations inside the mapping.

SUM(WIN_30_DUR) OVER(PARTITION BY AGENT_MASTER_ID ORDER BY ROW_DT ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING)

Basically this is sql(oracle) level requirement but i want at informatica level.

posted Jul 18, 2014 by Sachin

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

1 Answer

0 votes

Use the Aggregator to calculate sums groupped by AGENT_MASTER_ID and self-join it on AGENT_MASTER_ID. Make sure to have the data sorted and use 'sorted input' property for aggregator - it will also be mandatory for a self join.

answer Jul 23, 2014 by Shweta Singh
Similar Questions
+1 vote
How to implement Cume_Dist() analytic function logic in informatica

Pl help me here to implement CUME_DIST() logic in informatica. My requirement follows.

CUME_DIST ( ) OVER ( PARTITION BY completionmonth, product_industry_desc, dsa_region ORDER by LT_AVG_$$ desc)*100

My manager doesn't want sq override. We need to implement same in informatica with expression/Agrregator like.

Pl help how to and what that mean?

Thanks

+1 vote
How to specify Row Delimeter while writing to file in informatica 9

I have data with only 1 column and about 50 rows in it like

col
-----------
ab~~8900~~iop
nmk~~?~~ioiooo
king~~till~~8899089

the data will have column delimeters(i.e~~) but i will not seperate them. I have to clean the data and write to file after cleaning But the output file should be like

 col
 ------
 ab~~8900~~iop#^#nmk~~?~~ioiooo#^#king~~till~~8899089
The row delimeter here is #^#

How can i get this done.

Please suggest.

+1 vote
How to pivot row data using Informatica?

How can I pivot row data using Informatica PowerCenter Designer? Say, I have a source file called address.txt:

+---------+--------------+-----------------+
| ADDR_ID |     NAME     |     ADDRESS     |
+---------+--------------+-----------------+
|       1 | John Smith   | JohnsAddress1   |
|       1 | John Smith   | JohnsAddress2   |
|       2 | Adrian Smith | AdriansAddress1 |
|       2 | Adrian Smith | AdriansAddress2 |
+---------+--------------+-----------------+

I would like to Pivot this data like this:

+---------+--------------+-----------------+-----------------+
| ADDR_ID |     NAME     |    ADDRESS1     |    ADDRESS2     |
+---------+--------------+-----------------+-----------------+
|       1 | John Smith   | JohnsAddress1   | JohnsAddress2   |
|       2 | Adrian Smith | AdriansAddress1 | AdriansAddress2 |
+---------+--------------+-----------------+-----------------+

How can I do this in Informatica?

+1 vote
Convert multiple columns to one row in target table using Informatica

I have a source with multiple columns -
minA, maxA, minB, maxB, minC, maxC, minD, maxD

and I have 2 columns in target min values, max values. How can i put the min values of elements in min values of target and same with max?

...