Answers:Need suggestions - Informatica mapping to find department-wise top two salary without using rank transformation.

Hello readers,

Need your answers on this scenario.this question is asked  in Accenture technical interview .

Informatica Scenario : Remove special character from the string

Senario:

Suppose  in flat_file  some special symbols like @,%,$,#,&  has added in empno column along with the actual data. How to  remove those special charcters ? ( see article , on how to remove $ from salary )

example:  empno in source   

empno(in string format)

7@3%$,21432#@1324&*,$2

In target :empno

7321
4321
3242

Following are the steps for achieving this mapping :  

1. Connect 0/p columns of SQF to an expression transformation.
2. In expression make empno as input and create another port empno1 as output port with date datatype. 
3. And in empno1 write condition like this. and finally send it to target.

Your comments and suggests keeps me going, don't forget to do so.

 

Informatica Scenario : How to split the data of source table column-wise with respect to primary key

source table: ID is the key column, Name and Phone No are non-key columns

src table

source table: ID is the key column, Name and Phone No are non-key columns

source table	Target 1	Target 2
ID Name Phone No 10 AAA 123 20 BBB 234 30 CCC 434 40 DDD 343 50 EEE 442	ID Name 10 AAA 20 BBB 30 CCC 40 DDD 50 EEE	ID Phone No 10 123 20 234 30 434 40 343 50 442

Solution:

Step  1: Source qualifier: get the source table to the mapping area. See image below.

1

 Step 2:  Drag all the port from (from the previous step) to the Aggregator transformation and group by the key column. Since we have to split the columns to two different tables with the key column in each, so we are going use two expression transformation, each will take the key column and one non-key column. Connect  aggregator transformation with each of the expression transformation as follows.

2

Step 3: We need another set of  aggregator to be associated with each of the expression transformation from the previous step.

Step 4: In the final step connect the aggregators with the two target tables as follows.

3

 Here is the iconic view of the entire mapping.

4