Simple senario with solution

I have the source table is

  Number

1

1

2

3

3

4

Target_unique                   Target_Duplicates

    2                                                   1

    4                                                   3

Procedure :

>  Drag the source twise in the mapping designer.

> Drag the targets on the mapping designer.

> Create Aggregrator Transformation take the number as input port and count number take as output port with the condition is count(number).

> Create the joinner transformation drag the number port from source2 and drag the all ports from the aggregrator transformation.

> The condition in joinner transformation is number1=number.

> Create the router transformation with two groups

   *  Unique group have the condition is countnumber=1.

   * Duplicate group have the condition is countnumber>1.

> Link to the two targets.

> Create session and workflow.

CREATE THE SOURE TABLE AS

ID KITNO CODE
1  1      G1
2  2      G1
3  3      G1
4  4      G1
5  5      G2


TARGET TABLE IS

ID KITNO CODE
1  4      G1
2  4      G1
3  4      G1
4  4      G1
5  1      G2

 Procedure is

Drop the soure table twice in the mapping designer.
Drop the target table.
Create the aggregator transformation by the following logic
  1.Take kit no as input port.
  2. code as group by.
  3. create new port max count as output port with the condition is
     max(kit no).
Create the Normal joiner Transformation.
Drag the code and max count from the agg transformation.
Drag the id,kit no,code from the second source table.
create the condition in the joiner transformation code1=code.
Drag the ports from joiner transformation to target that are id,max count for kit no,code1 for code.
Create the session and work flow.                         

 

 

  Scenario - 2

Let say i have more than have record in source table and i have 3 destination table A,B,C. i have to insert first to 10 record in A then 11 to 20 in B 21 to 30 in c. Then again from 31 to 40 in A. 41 to 50 in B and 51 to 60 in c... so on up to last record.

Drag the source and target on maping desingner.

Create the exp transformations and copy the source ports to the exp transformation.
Double the exp trans and create new port seq_number.
Create the sequence generator transformation.
Link the nextvalue to the seq_number transfromation.
create Router transformation and drag the ports of exp transformation into router transoformation,
create the 3 groups and comming to logic is

Group1 = mod(seq_number,30) >= 1 and mod(seq_number,30) <= 10
Group2 = mod(seq_number,30) >= 11 and mod(seq_number,30) <= 20
Group3 = (mod(seq_number,30) >=21 and mod(seq_number,30) <= 29 ) or mod(seq_number,30) = 0
 

Connect the group one to first target,group two to the second target,third group to the third target.
Create the session and create workflow.

If i had source like unique & duplicate records like 1,1,2,3,3,4 then i want load unique records in one target like 2,4 and i want load duplicate records like 1,1,3,3

For Unique and Duplicate , we can go for same flow :

Source-->SrcQualifier-->Aggregator(group by this column and find count also)--> then use joiner (use 

the column from Source qualifier and join the data from Aggregator as source qualifier.col=aggregator.col) --> Router (insert into t1 for count>1 and for count=1 insert into t2). t1 is for duplicate records and it will cintain 1,1,3,3 and t2 will have 2,4.

Records in Source Q :

col 

1,1,2,3,3,4

The records after agregator will look like this :

col   count

1     2

2     1

3     2  

4     1

Records after Joiner : source q.col=aggregator.col

col count

1   2

1   2

2   1

3   2

3   2

4   1

These records will flow in router and we define two groups; one for count>1 (for duplicates record) and second for count=1 ( for unique record)

I have a scenario like - how can i load 1st record to Trgt1,2nd-Trgt2, 3rd->Trgt3 and again the cycle has to repeat with loading 4th-Trgt1,5th->Trgt2,6th->Trgt3?

Take a sequence generator with Start Value=1,Current 

Value=1,End Value=3, Increment By=1  to assign a seqno to 

each row. Do not forget to enable the cycle option.

after that take a Router with three groups of 

seqno=1,seqno=2 and seqno=3

How to load cumulative salary in to target ?

Using var ports in expression we can load cumulative salary into target.

If we want to run 2 workflow one after another(how to set the dependence between wf’s)

     

• If both workflow exists in same folder we can create 2 worklet rather than creating 2 workfolws.
• Finally we can call these 2 worklets in one workflow.
• There we can set the dependency.
• If both workflows exists in different folders or repository then we cannot create worklet.
• We can set the dependency between these two workflow using shell script is one approach.
• The other approach is event wait and event rise.

If both workflow exists in different folrder or different rep then  we can use below approaches.

1)      Using  shell script

• As soon as first workflow get completes we are creating zero byte file (indicator file).
• If indicator file is available in particular location. We will run second workflow.
• If indicator file is not available we will wait for 5 minutes and again we will check for the indicator. Like this we will continue the loop for 5 times i.e 30 minutes.
• After 30 minutes if the file does not exists we will send out email notification.

2)      Event wait and Event rise approach

We can put event wait before actual session run in the workflow to wait a indicator file if file available then it will run the session other event wait it will wait for infinite time till the indicator file is available.

How to populate file name to target while loading multiple files using list file concept.

      

In informatica 8.6 by selecting Add currently processed flatfile name option in the properties tab of source definition after import source file defination in source analyzer.It will add new column as currently processed file name.we can map this column to target to populate filename.

How to Process multiple flat files to single target table through informatica if all files are same structure?

We can process all flat files through one mapping and one session using list file.

First we need to create list file using unix script  for all flat file the extension of the list file is .LST.

This list file it will have only flat file names.

At session  level  we need to set

source file directory  as list file path

And source file name as list file name

And file type as indirect.

How to send Unique (Distinct) records into One target and duplicates into another tatget?

    

Source:

Ename	EmpNo
stev	100
Stev	100
john	101
Mathew	102

Output:

Target_1:

Ename	EmpNo
Stev	100
John	101
Mathew	102

Target_2:

Ename	EmpNo
Stev	100

Approch 1: Using Dynamic Lookup on Target table:

If record doen’t exit do insert in target_1 .If it is already exist then send it to Target_2 using Router.

Approch2: Using Var port :

Sort the data in sq  based on EmpNo column then Use expression to store previous record information using Var ports after that use router to route the data into targets  if it is first time then sent it to first target  if it is already inserted then send it to Tartget_2.

How to concatenate row data through informatica ?

  

Source:

Ename	EmpNo
stev	100
methew	100
john	101
tom	101

Target:

Ename	EmpNo
Stev methew	100
John tom	101

Approach1: Using Dynamic Lookup on Target table:

If record doen’t exit do insert in target .If it is already exist then get corresponding Ename vale from lookup  and concat in expression with current Ename value then update the target Ename column using update strategy.

Approch2: Using Var port :

Sort the data in sq  based on EmpNo column then Use expression to store previous record information using Var port after that use router to insert a record if it is first time if it is already inserted then update Ename with concat value of prev name and  current name value then update in target.

How to do Dymanic File generation in Informatica ?

I want to generate the separate file for every State (as per state, it should generate file).It has to generate 2 flat files and name of the flat file is corresponding state name that is the requirement.

Below is my mapping.

Source (Table) -> SQ -> Target (FF)

Source:

State	Transaction	City
AP	2	HYD
AP	1	TPT
KA	5	BANG
KA	7	MYSORE
KA	3	HUBLI

This functionality was added in informatica 8.5 onwards earlier versions it was not there.

We can achieve it with use of transaction control and special "FileName" port in the target file .

In order to generate the target file names from the mapping, we should make use of the special "FileName" port in the target file. You can't create this special port from the usual New port button. There is a special button with label "F" on it to the right most corner of the target flat file when viewed in "Target Designer".

When you have different sets of input data with different target files created, use the same instance, but with a Transaction Control transformation which defines the boundary for the source sets.

in target flat file there is option in column tab i.e filename as column.
when you click that one non editable column gets created in metadata of target.

in transaction control give condition as iif(not isnull(emp_no),tc_commit_before,continue) else tc_commit_before

map the emp_no column to target's filename column

ur mapping will be like this

source -> squlf-> transaction control-> target

run it ,separate files will be created by name of Ename

I have a scenario like - how can i load  1st record to Trgt1,2nd-Trgt2, 3rd->Trgt3 and again the cycle has to repeat with loading 4th-Trgt1,5th->Trgt2,6th->Trgt3?

Take a sequence generator with Start Value=1,Current 

Value=1,End Value=3, Increment By=1  to assign a seqno to 

each row. Do not forget to enable the cycle option.

after that take a Router with three groups of 

seqno=1,seqno=2 and seqno=3.

Lets say  i have more then have record in source table  and i have 3 destination table A,B,C. I have to insert first 1 to 10 record in A then 11 to 20 in B and 21 to 30 in C.
Then again from 31 to 40 in A, 41 to 50 in B and 51 to 60 in C……So on upto last record.

SOLUTION:

Generate sequence number using informatica, add filter or router transformations and define the conditions accordingly…

Define group condition as follows under router groups….

Group1 = mod(seq_number,30) >= 1 and mod(seq_number,30) <= 10
Group2 = mod(seq_number,30) >= 11 and mod(seq_number,30) <= 20
Group3 = (mod(seq_number,30) >=21 and mod(seq_number,30) <= 29 ) or mod(seq_number,30) = 0

Connect Group1 to A, Group2 to B and Group3 to C

when we use lookup,if have 5 records,if i dont need 1st and 2nd records..what will be the procedure to achieve by using lookup?

use lookup override as 

select *from emp minus select *from emp where rownum<=2

We have a target source table containing 3 columns : Col1, Col2 and Col3. There is only 1 row in the table as follows:(without using Normalizer)

Col1 Col2 Col3
-----------------
  a       b       c

There is target table containg only 1 column Col. Design a mapping so that the target table contains 3 rows as follows:

Col
-----
a
b
c

Solution: Not using a Normalizer transformation:

Create 3 expression transformations exp_1,exp_2 and exp_3 with 1 port each. Connect col1 from Source Qualifier to port in exp_1.Connect col2 from Source Qualifier to port in exp_2.Connect col3 from source qualifier to port in exp_3. Make 3 instances of the target. Connect port from exp_1 to target_1. Connect port from exp_2 to target_2 and connect port from exp_3 to target_3.

There is a source table that contains duplicate rows.Design a mapping to load all the unique rows in 1 target while all the duplicate rows (only 1 occurence) in another target.

Solution :

Bring all the columns from source qualifier to an Aggregator transformation. Check group by on the key column. Create a new output port count_col in aggregator transformation and write an expression count(key_column). Make a router transformation with 2 groups:Dup and Non-Dup. Check the router conditions count_col>1 in Dup group while count_col=1 in Non-dup group. Load these 2 groups in different targets.

Design an Informatica mapping to load first half records to 1 target while other half records to a separate target.

Solution:

You will have to assign a row number with each record. To achieve this, either use Oracle's psudo column rownum in Source Qualifier query or use NEXTVAL port of a Sequence generator. Lets name this column as rownumber.

From Source Qualifier, create 2 pipelines:

First Pipeline:
Carry first port Col1 from SQ transformation into an aggregator transformation. Create a new output port "tot_rec" and give the expression as COUNT(Col1). Do not group by any port. This will give us the total number of records in Source Table. Carry this port tot_rec to an Expression Transformation. Add another port DUMMY in expression transformation with default value 1.

Second Pipeline:from SQ transformation, carry all the ports(including an additional port rownumber generated by rownum or sequence generator) to an Expression Transformation. Add another port DUMMY in expression transformation with default value 1.

Join these 2 pipelines with a Joiner Transformation on common port DUMMY. carry all the source table ports and 2 additional ports tot_rec and rownumber to a router transformation. Add 2 groups in Router : FIRST_HALF and SECOND_HALF. Give condition rownumber<=tot_rec/2 in FIRST_HALF. Give condition rownumber>tot_rec/2 in SECOND_HALF. Connect the 2 groups to 2 different targets.

There is a source table containing 2 columns Col1 and Col2 with data as follows:


Col1 Col2

a l

b p

a m

a n

b q

x y


Design a mapping to load a target table with following values from the above mentioned source:

Col1 Col2

a l,m,n

b p,q

x y



Solution:



Use a sorter transformation after the source qualifier to sort the values with col1 as key. Build an expression transformation with following ports(order of ports should also be the same):



1. Col1_prev : It will be a variable type port. Expression should contain a variable e.g val

2. Col1 : It will be Input/Output port from Sorter transformation

3. Col2 : It will be input port from sorter transformation

4. val : It will be a variable type port. Expression should contain Col1

5. Concatenated_value: It will be a variable type port. Expression should be decode(Col1,Col1_prev,Concatenated_value

','

Col2,Col1)

6. Concatenated_Final : It will be an outpur port conating the value of Concatenated_value



After expression, build a Aggregator Transformation. Bring ports Col1 and Concatenated_Final into aggregator. Group by Col1. Don't give any expression. This effectively will return the last row from each group.


Connect the ports Col1 and Concatenated_Final from aggregator to the target table.