top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

Facebook Login
Site Registration

Informatica: Converting a table into multiple target tables based on one column.

+1 vote
673 views

In my source table data is

s_name,p_name,value
s1 ,   p1,     10
s1 ,   p2,     xyz
s1 ,   p3 ,    abc
s2 ,   p1 ,    20
s2 ,   p2 ,    xyz
s2 ,   p3 ,    abc

I want two target tables, first table is based on s_name s1, second table based on s_name s2. Both table contains contains p_name and value.

The target table data like as

Table s1

p_name,value 
p1,    10
p2,    xyz
p3,    abc 

Table s2

p_name,value 
p1,    20
p2,    xyz
p3,    abc 
posted Jun 9, 2014 by Jaineesh

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button
thank u venkat
sorry sir,if you get s3 record updated in table know what we are doing, thats why i think router transformation isn't correct.
i have only single target table and occuring multiple occuring tables

3 Answers

0 votes

Read the following document Creating Target Files

Read the following thread and let me know if you are not able to achieve whatever u wanted.
https://community.informatica.com/message/89759

answer Jun 10, 2014 by Tapesh Kulkarni
0 votes

As the first step let’s create the flat file definition using Target Designer. Add one new column 'FileName' using the 'Add File Name Column' button. This is the column based on the file name is dynamically changed.

Sort the data using the SORTER transformation with respect to s_name column.

Add an EXPRESSION transformation to set a boundary flag for each s_name group. In the expression transformation add following ports.
- VNEWFILEFLAG as Variable Port.
- VSTATE as Variable Port.
- NEWFILEFLAG as Output Port.
- FileName as Output Port.

And the following are the expressions for the ports.
- VNEWFILEFLAG :- IIF(s_name = VSTATE, 'N', 'Y')
- VSTATE :- s_name
- NEWFILEFLAG :- VNEWFILEFLAG
- FileName :- 'CustomerMaster' || s_name'

Based on the flag we created in the EXPRESSION transformation, we are going to split the file in the next step. We will use TRANSACTION CONTROL transformation to do the same. Set the 'Transaction Control Condition' property of the transformation with the below expression.
IIF(NEWFILEFLAG='Y',TCCOMMITBEFORE,TCCONTINUETRANSACTION)

answer Jun 11, 2014 by Shatark Bajpai
0 votes

Router transformation routes the rows of data into a separate output group based on conditions defined for each group.

For your example you will need two define two output groups with the following conditions:

group s1_rows - condition s_name = 's1'
group s2_rows - condition s_name = 's2'

answer Jun 13, 2014 by anonymous
Similar Questions
+1 vote

My source data

id    |   value
10       a,b,c
20       d,e,f

I want output is

id    |   value
10       a
10       b
10       c
20       d
20       e
20       f

How to do this scenario

+1 vote

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?

+1 vote

Couple of tables in our Database doesm't have any key. However, key was created in target table at informatica level.

Now my need is,I want to identify informatica metadata table where I can find target table and corresponding column name marked as key.

These tables are created long back,not sure why it was created like that. We planed to add primary key at database level so to identify the primary key .I want to look for informatica metadata tables

Kindly suggest

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