EASY

 

1.How do you remove Duplicate records in Informatica? And how many ways are there to do it?

There are several ways to remove duplicates.

1. If the source is DBMS, you can use the property in Source Qualifier to select the distinct records.Or you can also use the SQL Override to perform the same.
2. You can use, Aggregator and select all the ports as key to get the distinct values. After you pass all the required ports to the Aggregator, select all those ports , those you need to select for de-duplication. If you want to find the duplicates based on the entire columns, select all the ports as group by key.
   The Mapping will look like this.
   
3. You can use Sorter and use the Sort Distinct Property to get the distinct values. Configure the sorter in the following way to enable this.
   
4. You can use, Expression and Filter transformation, to identify and remove duplicate if your data is sorted. If your data is not sorted, then, you may first use a sorter to sort the data and then apply this logic:

• Bring the source into the Mapping designer.
• Let’s assume the data is not sorted. We are using a sorter to sort the data. The Key for sorting would be Employee_ID.
  Configure the Sorter as mentioned below.
  
• Use one expression transformation to flag the duplicates. We will use the variable ports to identify the duplicate entries, based on Employee_ID.
  
• Use a filter transformation, only to pass IS_DUP = 0. As from the previous expression transformation, we will have IS_DUP =0 attached to only records, which are unique. If IS_DUP > 0, that means, those are duplicate entries.
  
• Add the ports to the target. The entire mapping should look like this.
  

v. When you change the property of the Lookup transformation to use the Dynamic Cache, a new port is added to the transformation. NewLookupRow.

The Dynamic Cache can update the cache, as and when it is reading the data.

If the source has duplicate records, you can also use Dynamic Lookup cache and then router to select only the distinct one.

2.How do you load unique records into one target table and duplicate records into  a different target table? 

Source Table:

COL1	COL2	COL3
a	b	c
x	y	z
a	b	c
r	f	u
a	b	c
v	f	r
v	f	r

Target Table 1: Table containing all the unique rows

COL1	COL2	COL3
a	b	c
x	y	z
r	f	u
v	f	r

Target Table 2: Table containing all the duplicate rows

COL1	COL2	COL3
a	b	c
a	b	c
v	f	r

1. Drag the source to mapping and connect it to an aggregator transformation.
   
2. In aggregator transformation, group by the key column and add a new port. Call it count_rec to count  the key column.
3. Connect  a router to the aggregator from the previous step. In router make two groups: one named “original” and another as “duplicate”.
   In original write count_rec=1 and in duplicate write count_rec>1.
   The picture below depicts the group name and the filter conditions.
   Connect two groups to corresponding target tables
4. 
   

 

3.How to load unique or distinct records from flat file to target?

How to delete duplicate records or rather to select distinct rows for flat file sources?

 

 

Using Aggregator we can load unique records by setting group by on all columns this is one approach

Another method is we can use sorter after source qualifier in sorter  we can use distinct option.

 

Using Dynamic Lookup on Target table:

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

Using Var port Approach:

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.

Q 4. Consider the following source Data.

A

B

C

C

B

D

B

 

Design a Informatica mapping to load original and duplicate records in two different tables / Separating duplicate and non-duplicate rows.

The first table should contain the following output

A

B

C

D

The second table should contain the following output

B

B

C

Solution1: Use sorter transformation and sort the products data. Pass the output to an expression transformation and create a variable port,V_Current_product, and assign product port to it. Then create a V_Count port and in the expression editor write V_Count=IIF(V_Current_product=V_Previous_product,V_Count+1,1). Create one more variable port V_Previous_product and assign product port to it. Now create an output port O_Count port and assign V_Count port to it.

In the expression transformation, the ports are

Product

V_Current_product=product

V_Count=IIF(V_Current_product=V_Previous_product,V_Count+1,1)

V_Previous_product=product

O_Count=V_Count

The output of expression transformation will be

 

Product	O_Count
A	1
B	1
B	2
B	3
C	1
C	2
D	1

Now Pass the output of expression transformation to a router transformation, create one group and specify the condition as O_count=1. Then connect this group to one table. Connect the output of default group to another table.

Solution2: Next approach to do this is use aggregator, First sort the data using sorter then connect it to aggregator transformation group by product with an additional port COUNT_RECORD in expression put COUNT(Product).

Now with a router transformation create two groups namely DUPLICATE & ORIGINAL and give the group condition COUNT_RECORD > 1 & COUNT_RECORD =1 respectively.

In the Aggregator transformation ports are

Product

COUNT_RECORD=COUNT(Product)

In the Router Transformation group Conditions are

DUPLICATE group (COUNT_RECORD>1)

ORIGINAL group (COUNT_RECORD=1)

Connect Table 1 to DUPLICATE group and Table 2 to Original Group. There you go you have duplicate and original data separated.

check  how aggregator send unique and duplicate 

 

5. Consider the following products data which contain duplicate rows.

 

A
B
C
C
B
D
B

 Design a mapping to load all unique products in one table and the duplicate rows in another table.

The first table should contain the following output

A
D

The second target should contain the following output
B
B
B
C
C

Solution:
Use sorter transformation and sort the products data. Pass the output to an expression transformation and create a dummy port O_dummy and assign 1 to that port. So that, the DUMMY output port always return 1 for each row.

The output of expression transformation will be
Product, O_dummy
A, 1
B, 1
B, 1
B, 1
C, 1
C, 1
D, 1

Pass the output of expression transformation to an aggregator transformation. Check the group by on product port. In the aggreagtor, create an output port O_count_of_each_product and write an expression count(product).

The output of aggregator will be
Product, O_count_of_each_product
A, 1
B, 3
C, 2
D, 1

Now pass the output of expression transformation, aggregator transformation to joiner transformation and join on the products port. In the joiner transformation check the property sorted input, then only you can connect both expression and aggregator to joiner transformation.

The output of joiner will be
product, O_dummy, O_count_of_each_product
A, 1, 1
B, 1, 3
B, 1, 3
B, 1, 3
C, 1, 2
C, 1, 2
D, 1, 1

Now pass the output of joiner to a router transformation, create one group and specify the group condition as O_dummy=O_count_of_each_product. Then connect this group to one table. Connect the output of default group to another table.

 

 

 

 

6.How do you load alternate records into different tables through mapping flow?

 

The idea is to add a sequence number to the records and then divide the record number by 2. If it is divisible, then move it to one target and if not then move it to other target.

1. Drag the source and connect to an expression transformation.
2. Add the next value of a sequence generator to expression transformation.
   
3. In expression transformation make two port, one is “odd” and another “even”.
4. Write the expression as below
   
5. Connect a router transformation to expression.
6. Make two group in router.
7. Give condition as below
   
8. Then send the two group to different targets. This is the entire flow.
   

 

 

 

Loading Multiple Target Tables Based on Conditions- Suppose we have some serial numbers in a flat filesource. We want to load the serial numbers in two target files one containing the EVEN serial numbers and  the other file having the ODD ones.

After the Source Qualifier place a Router Transformation. Create two Groups namely EVEN and ODD, with
filter conditions as:

•  MOD(SERIAL_NO,2)=0
•  MOD(SERIAL_NO,2)=1

Then output the two groups into two flat file targets.

 

The mapping flow and the transformations used are mentioned below:

SRC->SQ->EXP->RTR->TGTS

• First create a new mapping and drag the source into the mapping.
• Create an expression transformation. Drag the ports of source qualifier into the expression transformation. Create the following additional ports and assign the corresponding expressions:

v_count (variable port) = v_count+1
o_count (output port) = v_count

• Create a router transformation and drag the ports (products, v_count) from expression transformation into the router transformation. Create an output group in the router transformation and specify the following filter condition:

MOD(o_count,2) = 1

• Now connect the output group of the router transformation to the target1 and default group to target2. Save the mapping.

In the above solution, I have used expression transformation for generating numbers. You can also use sequence generator transformation for producing sequence values. 

This is how we have to load alternative records into multiple targets.

 

How can we distribute and load ‘n’ number of Source records equally into two target tables, so that each
have ‘n/2’ records?
Answer:

•  After Source Qualifier use an expression transformation.
•  In the expression transformation create a counter variable
  V_COUNTER = V_COUNTER + 1 (Variable port)
  O_COUNTER = V_COUNTER (o/p port)
  This counter variable will get incremented by 1 for every new record which comes in.
•  Router Transformation:
  Group_ODD: IIF(MOD(O_COUNTER, 2) = 1)
  Group_EVEN: IIF(MOD(O_COUNTER, 2) = 0)
  Half of the record (all odd number record) will go to Group_ODD and rest to Group_EVEN.
•  Finally the target tables.

SUPPOSE WE HAVE ‘N’ NUMBER OF ROWS IN THE SOURCE AND WE HAVE TWO TARGET TABLES. HOW CAN WE LOAD ‘N/2’ I.E. FIRST
HALF THE SOURCE DATA INTO ONE TARGET AND THE REMAINING HALF INTO THE NEXT TARGET? 

 

7.How do you load first and last records into target table? How many ways are there to do it? Explain through mapping flows.

 

Solution:

Step  1: Drag and drop ports from source qualifier to two rank transformations.

Step  2: Create a reusable sequence generator having start value 1 and connect the next value to both rank transformations.

Step  3: Set rank properties as follows

In Rank1

In Rank2

Step  4: Make two instances of the target.

Step  5: Connect the output port to target.

 

8. I have 100 records in source table, but I want to load 1, 5,10,15,20…..100 into target table. How can I do this? Explain in detailed mapping flow.

This is applicable for any n= 2, 3,4,5,6… For our example, n = 5. We can apply the same logic for any n.

The idea behind this is to add a sequence number to the records and divide the sequence number by n (for this case, it is 5). If completely divisible, i.e. no remainder, then send them to one target else, send them to the other one.

1. Connect an expression transformation after source qualifier.
2. Add the next value port of sequence generator to expression transformation.
   
3. In expression create a new port (validate) and write the expression as in the picture below.
   
4. Connect a filter transformation to expression and write the condition in property as given in the picture below.
   
5. Finally connect to target.
   

 

9.How do you load only null records into target? Explain through mapping flow.

Let us say, this is our source

Cust_id	Cust_name	Cust_amount	Cust_Place	Cust_zip
101	AD	160	KL	700098
102	BG	170	KJ	560078
NULL	NULL	180	KH	780098

The target structure is also the same but, we have got two tables, one which will contain the NULL records and one which will contain non NULL records.

We can design the mapping as mentioned below.

SQ –> EXP –> RTR –> TGT_NULL/TGT_NOT_NULL
EXP – Expression transformation create an output port

O_FLAG= IIF ( (ISNULL(cust_id) OR ISNULL(cust_name) OR ISNULL(cust_amount) OR ISNULL(cust _place) OR ISNULL(cust_zip)), ‘NULL’,’NNULL’)
** Assuming you need to redirect in case  any of value is null

OR

O_FLAG= IIF ( (ISNULL(cust_name) AND ISNULL(cust_no) AND ISNULL(cust_amount) AND ISNULL(cust _place) AND ISNULL(cust_zip)), ‘NULL’,’NNULL’)
** Assuming you need to redirect in case all of  value is null

RTR – Router transformation two groups

Group 1 connected to TGT_NULL ( Expression O_FLAG=’NULL’)
Group 2 connected to TGT_NOT_NULL ( Expression O_FLAG=’NNULL’

 

10.How do you update the records with or without using Update Strategy?

We can use the session configurations to update the records. We can have several options for handling database operations such as insert, update, delete.

During session configuration, you can select a single database operation for all rows using the Treat Source Rows As setting from the ‘Properties’ tab of the session.

• Insert: – Treat all rows as inserts.
• Delete: – Treat all rows as deletes.
• Update: – Treat all rows as updates.
• Data Driven :- Integration Service follows instructions coded into Update Strategy flag rows for insert, delete, update, or reject.

Once determined how to treat all rows in the session, we can also set options for individual rows, which gives additional control over how each rows behaves. We need to define these options in the Transformations view on mapping tab of the session properties.

• Insert: – Select this option to insert a row into a target table.
• Delete: – Select this option to delete a row from a table.
• Update :- You have the following options in this situation:
  • Update as Update: – Update each row flagged for update if it exists in the target table.
  • Update as Insert: – Insert each row flagged for update.
  • Update else Insert: – Update the row if it exists. Otherwise, insert it.
• Truncate Table: – Select this option to truncate the target table before loading data.

Steps:

1. Design the mapping just like an ‘INSERT’ only mapping, without Lookup, Update Strategy Transformation.
2. First set Treat Source Rows As property as shown in below image.
   
3. Next, set the properties for the target table as shown below. Choose the properties Insert and Update else Insert.
   

These options will make the session as Update and Insert records without using Update Strategy in Target Table.

When we need to update a huge table with few records and less inserts, we can use this solution to improve the session performance.

The solutions for such situations is not to use Lookup Transformation and Update Strategy to insert and update records.

The Lookup Transformation may not perform better as the lookup table size increases and it also degrades the performance.

11.Targeting records of employees who joined in current month.

scenario: Insert   the records  of those employees who  have joined  in current month  and Reject other rows.

Source

E_NO       JOIN_DATE
    ——-       ———
      1        07-JUL-11
      2        05-JUL-11
      3        05-MAY-11
If the current month is july ,2011 then target  will be like this.

Target
   E_NO      JOIN_DATE
   ——-       ———
     1        07-JUL-11
     2        05-JUL-11

To insert current month records we have to follow these steps

 

1. Connect one update strategy transformation next to SQF.
   
2. In update strategy properties write the condition like this
   
3. Send required ports update strategy to target.

12.Insert and reject records using update strategy

Scenario:There is a emp table and from that table insert  the data to targt where sal<3000 and reject other rows.

Following are the steps for achieving it

1. connect out-puts from SQF to Update Strategy transformation.
   
2. In properties of  Update Strategy write the condition like this
   
3. Connect the Update Strategy to target

 

13.Explain in detail about SCD TYPE 1 through mapping.

 

SCD Type1 Mapping

The SCD Type 1 methodology overwrites old data with new data, and therefore does not need to track historical data.

1. Here is the source.
   
2. We will compare the historical data based on key column CUSTOMER_ID.
3. This is the entire mapping:
   
4. Connect lookup to source. In Lookup fetch the data from target table and send only CUSTOMER_ID port from source to lookup.
   
5. Give the lookup condition like this:
   
6. Then, send rest of the columns from source to one router transformation.
   
7. In router create two groups and give condition like this:
   
8. For new records we have to generate new customer_id. For that, take a sequence generator and connect the next column to expression. New_rec group from router connect to target1 (Bring two instances of target to mapping, one for new rec and other for old rec). Then connect next_val from expression to customer_id column of target.
   
9. Change_rec group of router bring to one update strategy and give the condition like this:
   
10. Instead of 1 you can give dd_update in update-strategy and then connect to target.

14. Explain in detail SCD TYPE 2 through mapping.

SCD Type2 Mapping

In Type 2 Slowly Changing Dimension, if one new record is added to the existing table with a new information then, both the original and the new record will be presented having new records with its own primary key.

1. To identifying new_rec we should and one new_pm? and one vesion_no.
2. This is the source:
   
3. This is the entire mapping:
   
4. All the procedures are similar to SCD TYPE1 mapping. The Only difference is, from router new_rec will come to one update_strategy and condition will be given dd_insert and one new_pm and version_no will be added before sending to target.
5. Old_rec also will come to update_strategy condition will give dd_insert then will send to target

IMPLEMENT SLOWLY CHANGING DIMENSION OF TYPE 2 WHICH WILL LOAD CURRENT RECORD IN CURRENT TABLE AND OLD DATA
IN LOG TABLE. 

15.How do you load more than 1 Max Sal in each Department through Informatica or write sql query in oracle?

SQL query:

You can use this kind of query to fetch more than 1 Max salary for each department.

SELECT * FROM (

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID, SALARY, RANK () OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) SAL_RANK FROM EMPLOYEES)

WHERE SAL_RANK <= 2

Informatica Approach:

We can use the Rank transformation to achieve this.

Use Department_ID as the group key.

In the properties tab, select Top, 3.

The entire mapping should look like this.

This will give us the top 3 employees earning maximum salary in their respective departments.

 

16. How do you convert single row from source into three rows into target?

We can use Normalizer transformation for this. If we do not want to use Normalizer, then there is one alternate way for this.

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

Col1	Col2	Col3
a	b	C

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

Col

a

b

c

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

17. I have three same source structure tables. But, I want to load into single target table. How do I do this? Explain in detail through mapping flow.

We will have to use the Union Transformation here. Union Transformation is a multiple input group transformation and it has only one output group.

1. Drag all the sources in to the mapping designer.
   
2. Add one union transformation and configure it as follows.Group Tab.
   Group Ports Tab.
   
3. Connect the sources with the three input groups of the union transformation.
   
4. Send the output to the target or via a expression transformation to the target.The entire mapping should look like this.
   

 

18.How to join three sources using joiner? Explain though mapping flow.

We cannot join more than two sources using a single joiner. To join three sources, we need to have two joiner transformations.

Let’s say, we want to join three tables – Employees, Departments and Locations – using Joiner. We will need two joiners. Joiner-1 will join, Employees and Departments and Joiner-2 will join, the output from the Joiner-1 and Locations table.

Here are the steps.

1. Bring three sources into the mapping designer.
   
2. Create the Joiner -1 to join Employees and Departments using Department_ID.
   
   
3. Create the next joiner, Joiner-2. Take the Output from Joiner-1 and ports from Locations Table and bring them to Joiner-2. Join these two data sources using Location_ID.
   
4. The last step is to send the required ports from the Joiner-2 to the target or via an expression transformation to the target table.
   

 

19.Write the Unconnected lookup syntax and how to return more than one column.

We can only return one port from the Unconnected Lookup transformation. As the Unconnected lookup is called from another transformation, we cannot return multiple columns using Unconnected Lookup transformation.

However, there is a trick. We can use the SQL override and concatenate the multiple columns, those we need to return. When we can the lookup from another transformation, we need to separate the columns again using substring.

As a scenario, we are taking one source, containing the Customer_id and Order_id columns.

Source:

We need to look up the Customer_master table, which holds the Customer information, like Name, Phone etc.

The target should look like this:

Let’s have a look at the Unconnected Lookup.

https://forgetcode.com/informatica/451-substr-to-find-substring

The SQL Override, with concatenated port/column:

Entire mapping will look like this.

We are calling the unconnected lookup from one expression transformation.

Below is the screen shot of the expression transformation.

After execution of the above mapping, below is the target, that is populated.

 

20.Convert Day No. to corresponding month and date of year

Scenario: Suppose you have a source is like this

Source
  E_NO    YEAR        DAYNO
   ——   ——— –         ———
  1          01-JAN-07     301
  2          01-JAN-08     200
Year column  is a  date and dayno is numeric that represents a day ( as in 365 for 31-Dec-Year). Convert the Dayno to corresponding year’s month and date and then send to targer.

Target 
  E_NO           YEAR_MONTH_DAY
   ——            ——— ———-
     1                  29-OCT-07
     2                  19-JUL-08

These are the basic steps for this scenario

https://forgetcode.com/Informatica/937-ADD-TO-DATE-Add-or-Subract-Days-Months-Years-to-Existing-Date

1. Connect  SQF with an expression transformation.
   
2. In expression create one o/p port c_year_mm_dd, make it to date type and in that port write the condition like this.
   
3. Finally send to target

 

21.Sending to target with days difference more than 2 days

Scenario: From the order_delivery table  insert  the records to target where , day  difference between  order_date and delivery_date is greater than 2 days. ( Note: see last article , where we discussed finding the time in hour between two dates)

Source
ORDER_NO    ORDER_DATE       DELIVERY_DATE
———                      ———                   ———
        2                        11-JAN-83               13-JAN-83
        3                        04-FEB-83               07-FEB-83
        1                        08-DEC-81              09-DEC-81

Target
 ORDER_NO    ORDER_DATE   DELIVERY_ DATE    
 ———             ——– ——    — ———-
  2                      11-JAN-83       13-JAN-83         
  3                      04-FEB-83       07-FEB-83  

These are the steps for achieving this scenario

https://forgetcode.com/informatica/940-date-diff-find-time-interval-between-two-dates-in-year-months-or-days-or-in-hours

https://forgetcode.com/informatica/1056-trunc-truncate-day-or-month-or-year-part-in-datetime  use?

1. Connect all the rows from SQF to update strategy transformation.
   
2. In update strategy properties write the expression like this
   
3. Finally send to target.

 

22.Date Difference in Hours

 

Scenario:There is a order_delivery table having record like this
   ORDER_NO      ORDER_DATE      DELIVERY_DATE
   ———                        ———             ——–
        2                        11-JAN-83          13-JAN-83
        3                         04-FEB-83        07-FEB-83
        1                         08-DEC-81        09-DEC-81

We have to calculate difference between order_date and delivery date in hours and send it to target.
o/p will be

 ORDER_NO   ORDER_DATE   DELIVERY_ DATE      DIFF_IN_HH
 ———                     ———                  ———            ———
     2                       11-JAN-83            13-JAN-83              48
     3                       04-FEB-83           07-FEB-83             72
     1                       08-DEC-81           09-DEC-81             24
These are the steps for achieving this scenario

1. Connect one expression transformation next to SQF.
   
     
2. In expression create one out/put port “diff” and make it integer type.
   
3. In that port write the condition like this and sent to target.
   

 

23.Check the Hire-Date is Date or Not

 

Scenario:Suppose we have a table with records like this
 EMPNO        HIRE_DATE           
—— —-       ———–
     1             12-11-87
     2             02-04-88;
     3             02-2323
empno is number and hire_date is in string format. We have to check the hire_date column, if it is in date format like ‘dd-mm-yy’, then convert it to date , in the  format  “mm/dd/yy”  and send it to target  else send null.
output
    EMPNO       HIRE_DATE
    ——–          ———
       1             11-DEC-87
       2                null
       3               null

These are the steps for achieving this scenario

https://forgetcode.com/informatica/1064-is-date-check-for-valid-date

1. Connect the ports from SQF to an expression transformation.
   
2. In expression create another oupput port hire_date1 and make it to date data-type, shown in picture.
   
3. In Hire_date1 write the condition like this.
   
4. Send ports to target.

 

24.Extracting Middle Name From Ename

Suppose in e_name column is like this

empno            ename

1                   Sudhansu Sekher Dash

2                   Amiya Prasad Mishra

In target we have to send middle name like this

empno      ename

1              Sekher

2              Prasad

These are the steps for achieving this

https://forgetcode.com/informatica/451-substr-to-find-substring

https://forgetcode.com/informatica/1131-INSTR-Search-for-a-String

1. Drag the source and connect to an expression transformation
   
2. In Expression create two ports one is name1(as variable port) and Middle_Name (o/p port)
   
3. In Name1 write the condition like this
   
4. In Middle_Name write the condition like this
   
5. Then send to target.

 

25.Extracting first and last name

Suppose In Ename column there is first name and last name like this

empno   ename

1         Amit Rao

2         Chitra Dash

In target we have to separate ename column to firstnane and lastname like this

empno    firstname    Lastname

1             Amit           Rao

2             Chitra          Dash

Steps for solving this scenario

1. Drag the source to mapping area and connect with an expression trans formation as shown bellow.
   
2. In expression transformation create two output port one is f_name and other is l_name.
   
3. In f_name write the condition like this
   
4. In l_name write the condition like this
   

Then connect the target.

 

26.Convert Numeric Value to Date Format

 

Scenario: Suppose you are importing a flat file emp.csv and hire_date colummn is in numeric format, like 20101111 .Our objective is convert it to date,with a format ‘YYYYMMDD’.
 

source
  EMPNO       HIRE_DATE(numeric)           
  ——-            ———–
     1                20101111
     2                20090909
target
EMPNO            HIRE_DATE (date)          
 ——                   ———–
     1                   11/11/2010
     2                    09/09/2009

https://forgetcode.com/informatica/466-to-date-convert-string-to-date

1.  Connect SQF to an expression.
   
2. In expression make hire_date as input only and make another port hire_date1 as o/p port with date data type.
   
3. In o/p port of hire_date write condition like as below
   
4. Finally send to target

 

27.Remove special characters from empno

Scenario: Suppose  in flat_file  some special symbols like @,%,$,#,&  has added in empno column along with the actual data. How to  remove those special characters ? 
example
empno in source   
empno(in string format)
7@3%$,21
432#@1
324&*,$2
In target
empno
7321
4321
3242
Following are the steps for achieving this mapping

https://forgetcode.com/informatica/1816-replacechr-replace-characters-in-a-string-with-some-other-character-or-remove-them

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. And in empno1 write condition like this. and finally send it to target
   

 

28.Count the no of vowel present in emp_name column

https://forgetcode.com/informatica/1816-replacechr-replace-characters-in-a-string-with-some-other-character-or-remove-them

Scenario:Count the no of vowels present in emp_name column of EMP table as shown bellow.
     emp_name         total_vowels_count
       Allen                          2
       Scott                          1
       Ward                         1

These are the steps to achieve it

1. Connect required columns from SQF to an expression transformation.
   
2.  In Expression add 6 columns like in the picture as bellow. But You can make it two columns( One for all the vowels and one for the vowel counts). For better understanding I have added 6 columns,5 for each of the vowels and one for the vowel count.
   
   The way I achieved is for each of the vowels in ename , I replaced it with null and in port total vowel count , I  substract the vowel port from the ename length which gives me the individual count of vowels, after adding up for all vowels I found all the vowels present. Here are  all the  variable ports.
   For A  write                              REPLACECHR(0,ENAME,’a’,NULL)
   For E  write                             REPLACECHR(0,ENAME,’e’,NULL)
   For I  write                              REPLACECHR(0,ENAME,’i’,NULL)
   For O  write                            REPLACECHR(0,ENAME,’o’,NULL)
   For U  write                           REPLACECHR(0,ENAME,’u’,NULL)
   And for o/p column total_vowels_count write expression  like this
   (length(ENAME)-length(A))
   +
   (length(ENAME)-length(E))
   +
   (length(ENAME)-length(I))
   +
   (length(ENAME)-length(O))
   +
   (length(ENAME)-length(U))
   
3. Finally send to target.

This could have been done a different or a better way, If you have suggestion don’t forget to comment.

 

29.Converting ‘$’ symbol to ‘RS.” in sal column

The Emp table contains the salary and commission in USD, in the target the com and sal will converted to a given currency prefix ex: Rs.

 Source

EMPNO ENAME      JOB              MGR HIREDATE         SAL            DEPTNO 

7369 SMITH            CLERK           7902 17-DEC-80        $800                20              

7499 ALLEN      SALESMAN        7698 20-FEB-81         $1600               30              

Target

EMPNO ENAME      JOB              MGR HIREDATE         SAL                 DEPTNO 

7369 SMITH            CLERK           7902 17-DEC-80        Rs.800                20    

7499 ALLEN      SALESMAN        7698 20-FEB-81          RS.1600               30

 

https://forgetcode.com/informatica/1131-INSTR-Search-for-a-String

https://forgetcode.com/informatica/451-substr-to-find-substring

1. Drag the source and connect it to expression transformation
   
2. In expression make a output port sal1 and make sal as input port only.
   
3.  In sal1 write the condition as like bellow
   
4.  Then send it to target.

 

30.sending data one after another to three tables in cyclic order

 In source  there are some record. Suppose I want to send three targets. First record will go to first target, Second one will go to second target and third record will go to third target and then 4th to 1st,5th to 2nd , 6th to  3rd and so on.

1. Put the source to mapping and connect it to an expression transformation.
   
2. Drag an sequence generator transformation and set properties like this And connect  the next value port to expression.
   
3. Drag all output port of expression to router. In router make three groups and gve the conditions Like this 
4. connect desire group to desire target .

Suppose we have a source table and we want to load three target tables based on source rows such that first row moves to first target table, second row in second target table, third row in third target table, fourth row again in first target table so on and so forth. Describe your approach.

We can clearly understand that we need a Router transformation to route or filter source data to the three target tables. Now the question is what will be the filter conditions.
First of all we need an Expression Transformation where we have all the source table columns and along with that we have another i/o port say seq_num, which gets sequence numbers for each source row from the port NEXTVAL of a Sequence Generator start value 0 and increment by 1.
Now the filter condition for the three router groups will be:

•  MOD(SEQ_NUM,3)=1 connected to 1st target table
•  MOD(SEQ_NUM,3)=2 connected to 2nd target table
•  MOD(SEQ_NUM,3)=0 connected to 3rd target table

 

 

31.Currency convertor

Suppose that a source contains a column which holds the salary information prefixed with the currency code , for example  
EMPNO ENAME      JOB              MGR HIREDATE         SAL            DEPTNO  
7369 SMITH            CLERK           7902 17-DEC-80        $300               20               
7499 ALLEN      SALESMAN        7698 20-FEB-81         £1600               30               
7521 WARD       SALESMAN        7698 22-FEB-81        ¥8500                30       
In the target different currency will evaluate to a single currency value, for  example covert all to Rupees.

1. First thing we should consider that there are different types of currency like pound, dollar, yen etc.So it’s a good idea to use mapping parameter or variable.Go to mapping=> mapping parameter and variables then create three parameters (for this example) and set its initial value as bellow
   
2. Then drag the source to mapping area and connect to an expression transformation.
   
3. In expression create a output port as sal1 and make sal as input only as bellow.
   
4. In sal1 port write the condition as below
   

   iif(instr(SAL,’$’)!=0,TO_integer(SUBSTR(SAL,INSTR(SAL,’$’)+1,LENGTH(SAL)-1))*$$DOLAR,
   iif(instr(SAL,’£’)!=0,TO_integer(SUBSTR(SAL,INSTR(SAL,’£’)+1,LENGTH(SAL)-1))*$$POUND,
   iif(instr(SAL,’¥’)!=0,TO_integer(SUBSTR(SAL,INSTR(SAL,’¥’)+1,LENGTH(SAL)-1))*$$YEN
   )
   )
   )
   $$DOLAR,$$POUND,$$YEN these are mapping parameter . you can multiply price in rupee directly  for example dollar price in rupees i.e 48 .

5. Connect required output port from expression to target directly. And run the session.

32.Removing ‘$’ symbol from salary column

: Reading a source file with salary prefix $ , in the target the Sal column must store in number .
Source
EMPNO ENAME      JOB              MGR HIREDATE         SAL            DEPTNO  
7369 SMITH            CLERK           7902 17-DEC-80        $800                20               
7499 ALLEN      SALESMAN        7698 20-FEB-81         $1600               30               
Target
EMPNO ENAME      JOB              MGR HIREDATE         SAL            DEPTNO  
7369 SMITH            CLERK           7902 17-DEC-80        800                20               
7499 ALLEN      SALESMAN        7698 20-FEB-81         1600               30     

1. Drag the source to mapping area and connect each port to an expression transformation.
   
2. In expression transformation add a new col sal1 and make it as out put and sal as in put  only as shown in picture.
   
3. In expression write the condition like this.
   
4.  connect the required port to target.

 

33.Using mapping parameter and variable in mapping

Scenario:How to use mapping parameter and variable in mapping ?

Solution:

1. Go to mapping then parameter and variable tab in the Informatica designer.Give name as $$v1, type choose parameter (You can also choose variable), data type as integer and give initial value as 20.
   
2. Create a mapping as shown in the figure( I have considered a simple scenario where a particular department id will be filtered to the target).
   
3. In filter set deptno=$$v1 (that means only dept no 20 record will go to the target.)
   
4. Mapping parameter value can’t change throughout the session but variable can be changed. We can change variable value by using text file. 

 

34.Validating all mapping in repository

Scenario:How validate all mapping in repository ?

Solution:

1. In repository go to menu “tool” then “queries”. Query Browser dialog box will appear.Then click on new button.
   
   
2. In Query Editor,  choose folder name and object type as I have shown in the picture.
   
3. After that, execute it (by clicking the blue arrow button).
4. Query results window will appear. You select single mapping (by selecting single one) or whole mapping (by pressing Ctrl + A) and go to “tools” then “validate” option to validate it.
   

 

35.Target table rows , with each row as sum of all previous rows from source table.

Scenario: How to produce rows in target table with every row  as sum of all previous rows in source table ? See the source and target table to understand the scenario.

 

SOURCE TABLE
id	Sal
1	200
2	300
3	500
4	560

TARGET TABLE
Id	Sal
1	200
2	500
3	1000
4	1560

1. Pull the source to mapping and then connect it to expression.
   
2. In expression add one column and make it output(sal1) and sal port as input only.
   We will make use of a function named cume() to solve our problem, rather using any complex mapping.  Write the expression in sal1 as cume(sal) and send the output rows to target.

 

 

 

36.Sending records to target tables in cyclic order

Scenario: There is a source table and 3 destination table T1,T2, T3. How  to insert first 1 to 10 record in T1, records from 11 to 20 in T2 and 21 to 30 in T3.Then again from 31 to 40 into T1, 41 to 50 in T2 and 51 to 60 in T3 and so on i.e in cyclic order.

Solution:

1. Drag the source and connect to an expression.Connect the next value port of sequence generator to expression.
2. 
3. Send the all ports to a router and make three groups as bellow
   Group1Group2Group3
4. mod(NEXTVAL,30) >= 21 and mod(NEXTVAL,30) <= 29 or mod(NEXTVAL,30) = 0
5. mod(NEXTVAL,30) >= 11 and mod(NEXTVAL,30) <= 20
6. mod(NEXTVAL,30) >= 1 and mod(NEXTVAL,30) <= 10
7. 
8. Finally connect Group1 to T1, Group2 to T2 and Group3 to T3.

 

37.Segregating rows on group count basis

Scenario : There are 4 departments in Emp table. The first one with 100,2nd with 5, 3rd with 30 and 4th dept has 12 employees. Extract those dept numbers which has more than 5 employees in it,  to a target table.

Solution:

https://forgetcode.com/informatica/1448-count-number-of-rows-with-not-null-values

1. Put the source to mapping and connect the ports to aggregator transformation.
   
2. Make 4 output ports in aggregator  as in the picture above : count_d10, count_d20, count_d30, count_d40.
   For each port write expression like in  the picture below.
   
3. Then send it to expression transformation.
   
4. In expression make four output ports (dept10, dept20, dept30, dept40) to validate dept no
   And provide the expression like in the picture below.
   
5. Then connect to router transformation. And create a group and fill condition like below.
   
6. Finally connect to target table having one column that is dept no.
   
    

38.Get top 5 records to target without using rank

 

Scenario : How to get top 5 records to target without using rank ?

Solution:

1. Drag the source to mapping and connect it to sorter transformation.

2. Arrange the salary in descending order in sorter as follows and send the record to expression.

sorter properties

3. Add the next value of sequence generator to expression.(start the value from 1 in sequence generator).

sorter to exp mapping

4. Connect the expression transformation to a filter or router. In the property set the condition as follows-

5. Finally connect to the target.

final mapping sc12

 

39.Separate rows on group basis

Scenario : In Dept table there are four departments (dept no 40,30,20,10). Separate the record to different target department wise.

Solution:

Step 1: Drag the source to mapping.
Step 2: Connect the router transformation to source and in router make 4 groups and give condition like below.

router transformation

Step 3: Based on the group map it to different target.

The final mapping looks like below.

router to target

 

40.Separate the original records in target

Scenario 10: How to separate  the original records from source table to separate target table by using rank  transformation ?

Source Table

col1	col2	col3
a	b	c
x	y	z
a	b	c
r	f	u
a	b	c
v	f	r
v	f	r

 

 

 

 

 

 

 

 

Target Table

Col1	Col2	Col3
a	b	c
x	y	z
r	f	u
v	f	r

 

 

 

 

 

Solution: 
Step 1: Bring the source to mapping.

src to rank mapping

Step 2: Connect the rank to source.
Step 3: In rank, set the property like this.

rank property

Step 4: Then send it to target.
Run the session to see the result.

 

41.Sending second half record to target

Scenario 8: How to send second  half record to target?

Solution 
Step 1: Drag and drop the source to mapping.

src to tgt mapping

Step 2: In source-Qualifier  , go to property and write the SQL query like

 

1	select * from emp minus select * from emp where rownum <= ( select count(*)/2 from emp))

src qualifier sql query

Step:3 Then connect to target, and run mapping to see the results.

42.Sending first half record to target

Scenario 6: How to send first half record to target?

Solution:

1. 1.  Drag and drop the source to mapping.
      
      
   2. Step:2 In source-Qualifier  , go to property and write the SQL query like
      
       

      1	select * from emp where rownum <= ( select count(*)/2 from emp)

      
      
   3. Then connect to target.Now you are ready to run the mapping to see it in action.

43.Remove header from your file

Scenario 6: How to remove header from a file ?

Solution

Step1:  After mapping  go to workflow  and scheduled it.
Step2:  Just double click on the session  and go to mapping option.

Step3:  Select  the source  and go to the set file properties.

flat file properties

Step4:Chose the advance option.  Set number of initial rows skip: 1 ( it can be more as per requirement )

adv properties

It will skip the header.

44.Remove footer from your file

 

Scenario 5: How to remove footer from your file ?

For example the file content looks like as below:-

some Header here
col1    col2    col3     col4
data1  data2  data3  data4
data5  data6  data7  data8
data1  data2  data3  data4
data1  data2  data3  data4
footer

Just we have to remove footer  from the file.

Solution:

Step1:  Drag the source to mapping area.

Step2: After that  connect a filter or router transformation.

Step3:   In filter write the condition  like  in the picture

Step 4:Finally  pass it over to target.

 

How to Separate Header and Footer from Flat file

:To load the Header records into one target table, say TRGT01, footer records to TRGT02 and detail records to another TRGT03

Source File

Header”ABC”

@@@@@@@@@@@@

Detail”123”

Detail”456”

Detail”789”

Detail”012”

@@@@@@@@@@@@

Footer”Efg”

1.Create Source definition for the file and connect to an Expression

2.In Expression create two variable ports

• V_HEADER= IIF (SUBSTR(COLUMN,1,1)=’Header’,1,0)
• V_FOOTER= IIF (SUBSTR(COLUMN,1,1)= ‘Footer’,1,0)

Also create two output ports

• O_HEADER=V_HEADER
• O_FOOTER=V_FOOTER

3.Create a Router with two user groups

• In first group give condition such that IIF(O_HEADER)=’1′ then pass to TRGT01
• In second group IIF(O_FOOTER)=’1′ then pass to TRGT02
• And the default group to TRGT3(this will hold the detail records)

Note: One more option is to use the Header Options in the Session Properties

45.Split the non-key columns to separate tables with key column in both discuss

Split the non-key columns to separate tables with key column in both  / How to split the data of source table column-wise with respect to primary key. See the source and target tables below.

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

 

ID	Name	Phone No
10	AAA	123
20	BBB	234
30	CCC	434
40	DDD	343
50	EEE	442

Target Table 1

ID	Name
10	AAA
20	BBB
30	CCC
40	DDD
50	EEE

Target Table 2

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.

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.

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.

Here is the iconic view of the entire mapping.

 

 

 

 

 46.Design a mapping to convert row data into column data.
The source data looks like

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

The target table data should look like

id, col1, col2, col3
10, a, b, c
20, d, e, f

Solution:

Step1: Use sorter transformation and sort the data using id port as the key. Then connect the sorter transformation to the expression transformation.

Step2: In the expression transformation, create the ports and assign the expressions as mentioned below.

id
value
V_curr_id=id
V_count= IIF(v_curr_id=V_prev_id,V_count+1,1)
V_prev_id=id
O_col1= IIF(V_count=1,value,NULL)
O_col2= IIF(V_count=2,value,NULL)
O_col3= IIF(V_count=3,value,NULL)

Step3: Connect the expression transformation to aggregator transformation. In the aggregator transformation, create the ports and assign the expressions as mentioned below.

id (specify group by on this port)
O_col1
O_col2
O_col3
col1=MAX(O_col1)
col2=MAX(O_col2)
col3=MAX(O_col3)

Stpe 4: Now connect the ports id, col1, col2, col3 from aggregator transformation to the target table.

 

 

 

47. Consider the following employees data as source

  employee_id, salary
  10, 1000
  20, 2000
  30, 3000
  40, 5000 

Let us assume the following Sales Data

ID	PERSONAL_SALES
10	40000
20	80000
30	40000
40	60000
50	NULL
60	50000

.Cumulative Sum Calculation in Informatica

 

Q) How to find the cumulative sum of salaries of employees in informatica?

 

I have employees table as a source. The data in the employees table is shown below: 

Table name: employees

Dept_Id, emp_id, salary
---------------------
10,  201, 10000
10,  202, 20000
10,  203, 30000
20,  301, 40000
20   302, 50000

I want to sort the data on the department id, employee id and then find the cumulative sum of salaries of employees in each department. The output i shown below: 

Dept_id emp_id salary, Cum_salary
---------------------------------
10,  201, 10000, 10000
10,  202, 20000, 30000
10,  203, 30000, 60000
20,  301, 40000, 40000
20   302, 50000, 90000

Solution: Follow the below steps for implementing mapping logic in informatica.

• Connect the source qualifier transformation to a sorter transformation. Sort the rows on the dept_id and emp_id ports in ascending order.
• Connect the sorter transformation to the expression transformation. In the expression transformation, create the following additional ports and assign the corresponding expressions:

v_salary (variable port) = IIF(dept_id = v_last_dept_id, v_salary + salary, salary)
v_last_dept_id (variable port) = dept_id
o_cum_salary (output port) = v_salary

• Connect the expression transformation ports to the target. Save the mapping.

 

. Design a Informatica mapping to calculate the cumulative sum of PERSONAL_SALES and load that into target table?

The Expected Output in target should be

ID	PERSONAL_SALES	O_RETURN_VALUE
10	40000	40000
20	80000	120000
30	40000	160000
40	60000	220000
50	NULL	220000
60	50000	270000

Solution:

Connect the source Qualifier to expression transformation.In the expression transformation create a variable port named V_RETURN_VALUE and write the expression CUME(PERSONAL_SALES), again create a output port O_RETURN_VALUE and assign the expression V_RETURN_VALUE to it. Connect all the output ports to target and run the mapping.

 

In the expression transformation,the ports are

ID                                            PERSONAL_SALES                                V_RETURN_VALUE = CUME(PERSONAL_SALES)  O_RETURN_VALUE =  V_RETURN_VALUE

// Input/Output Port // Input/Output Port // Variable Port // Output Port

Connect the output port from the expression transformation to the target and see the results.

 

48.Design a mapping to get the previous row salary for the current row. If there is no previous row exists for the current row, then the previous row salary should be displayed as null.
The output should look like as

employee_id, salary, pre_row_salary
10, 1000, Null
20, 2000, 1000
30, 3000, 2000
40, 5000, 3000

Solution:

Connect the source Qualifier to expression transformation. In the expression transformation, create a variable port V_count and increment it by one for each row entering the expression transformation. Also create V_salary variable port and assign the expression IIF(V_count=1,NULL,V_prev_salary) to it . Then create one more variable port V_prev_salary and assign Salary to it. Now create output port O_prev_salary and assign V_salary to it. Connect the expression transformation to the target ports.

In the expression transformation, the ports will be

employee_id
salary
V_count=V_count+1
V_salary=IIF(V_count=1,NULL,V_prev_salary)
V_prev_salary=salary
O_prev_salary=V_salary

 

ID                                       PERSONAL_SALES                           V_COUNT=V_COUNT+1                        V_SALES=IIF(V_COUNT=1,NULL,V_PREV_SALES) V_PREV_SALES=PERSONAL_SALES              O_RETURN_VALUE=V_SALES

// Input/Output Port // Input/Output Port // Variable Port // Variable Port // Variable Port // Output Port

.Get Previous Row Value in Informatica

 The source data is shown below:

Table Name: Customers

cust_id, Year, City
-----------------------
10,      2001, BLR
10,      2002, MUM
10,      2003, SEA
10,      2004, NY

20,      2001, DEL
20,      2002, NCR
20,      2003, HYD

The question is for each customer when processing the record for current row, you have to get the previous row city value. If there is no previous row, then make the previous row value as null. The output data is shown below:

Table Name: Customers_TGT

cust_id, Year, City, prev_city
------------------------------
10,      2001, BLR,  NULL
10,      2002, MUM,  BLR
10,      2003, SEA,  MUM
10,      2004, NY,   SEA

20,      2001, DEL,  NuLL,
20,      2002, NCR,  DEL
20,      2003, HYD,  NCR

Getting Previous Row Value Informatica Mapping Logic

Solution:

• Connect the source qualifier transformation to the sorter transformation and sort the data on cust_id, year ports in ascending order.
• Connect the sorter transformation to the expression transformation. In the expression transformation, create the below additional ports and assign the corresponding expressions:

cust_id  (input/output port)
year     (input/output port)
city     (input/output port)

v_current_cust_id   (variable port) = cust_id
v_act_previous_city (variable port  ) = IIF(v_current_cust_id = v_previous_cust_id, v_previous_city, NULL)
v_previous_city     (variable port) = city
v_previous_cust_id  (variable port) = cust_id
o_previous_city     (output port) = v_act_previous_city

 

• Connect the output ports of expression transformation to the target.

 

 

https://www.folkstalk.com/2011/11/informatica-scenario-based-questions.html

same as

https://www.scribd.com/document/248036211/129775529-Informatica-Scenario-Based-Interview-Questions-With-Answers-1

 

http://informatica-tutorials-for-beginners.blogspot.com/p/informatica-questions-part-.html#

49. Design a mapping to get the next row salary for the current row. If there is no next row for the current row, then the next row salary should be displayed as null.
The output should look like as

employee_id, salary, next_row_salary
10, 1000, 2000
20, 2000, 3000
30, 3000, 5000
40, 5000, Null

 

ID	PERSONAL_SALES	O_RETURN_VALUE
10	40000	80000
20	80000	40000
30	40000	60000
40	60000	NULL
50	NULL	50000

Solution:

1. Create a source qualifier transformation and connect it to two separate expression transformation. In each of the expression transformation create a variable port V_COUNT and increment it by 1 using expression V_COUNT+1. Then create an output port O_COUNT in each expression transformation. Assign the value V_COUNT in 1st expression and V_COUNT-1 in the 2nd expression as shown below.

In the 1st expression transformation, the ports will be

ID                                            PERSONAL_SALES                                V_COUNT=V_COUNT+1  O_COUNT=V_COUNT

// Input/Output Port // Input/Output Port // Variable Port // Output Port

1st Expression Data will look like

ID	PERSONAL_SALES	O_COUNT
10	40000	1
20	80000	2
30	40000	3
40	60000	4
50	NULL	5

In the 2nd expression transformation, the ports will be

ID                                            PERSONAL_SALES                                V_COUNT=V_COUNT+1   O_COUNT=V_COUNT-1

// Input/Output Port // Input/Output Port // Variable Port // Output Port

2nd Expression Data will look like

ID	PERSONAL_SALES	O_COUNT
10	40000	0
20	80000	1
30	40000	2
40	60000	3
50	NULL	4

2. Connect both the expression transformations ports to joiner transformation and join them on the port O_count. Consider the 1st expression transformation as Master and 2nd one as Detail Source. Specify the join type in Joiner transformation as Detail Outer Join. In the joiner transformation check the property sorted input this will allow you to connect both expression transformations to joiner transformation. 

3. Pass the output of joiner transformation to a target table. In the Joiner transformation, connect the ID and PERSONAL_SALES from the 1st expression transformation to the ID and PERSONAL_SALES in target table. Connect the PERSONAL_SALES which is coming from the 2nd expression transformation to the next row sales port in the target table.

 

50.Design a Informatica mapping to calculate the Moving sum of PERSONAL_SALES for two rows and load that into target table?

The Expected Output in target should be

ID	PERSONAL_SALES	O_RETURN_VALUE
10	40000	NULL
20	80000	120000
30	40000	120000
40	60000	100000
50	NULL	60000
60	50000	50000

Solution:

Connect the source Qualifier to expression transformation.In the expression transformation create a variable port named V_RETURN_VALUE and write the expression MOVINGSUM(PERSONAL_SALES,2) again create a output port O_RETURN_VALUE and assign the expression V_RETURN_VALUE to it. Connect all the output ports to target and run the mapping.

 

In the expression transformation,the ports are

ID                                            PERSONAL_SALES                                V_RETURN_VALUE = MOVINGSUM(PERSONAL_SALES,2)  O_RETURN_VALUE =  V_RETURN_VALUE

// Input/Output Port // Input/Output Port // Variable Port // Output Port

Connect the output port to the target and see the results 

51.Design a mapping to find the sum of salaries of all employees and this sum should repeat for all the rows.
The output should look like as

employee_id, salary, salary_sum
10, 1000, 11000
20, 2000, 11000
30, 3000, 11000
40, 5000, 11000

Solution:

Step1: Connect the source qualifier to the expression transformation. In the expression transformation, create a dummy port and assign value 1 to it.

In the expression transformation, the ports will be

employee_id
salary
O_dummy=1

Step2: Pass the output of expression transformation to aggregator. Create a new port O_sum_salary and in the expression editor write SUM(salary). Do not specify group by on any port.

In the aggregator transformation, the ports will be

salary
O_dummy
O_sum_salary=SUM(salary)

Step3: Pass the output of expression transformation, aggregator transformation to joiner transformation and join on the DUMMY port. In the joiner transformation check the property sorted input, then only you can connect both expression and aggregator to joiner transformation.

Step 4: Pass the output of joiner to the target table.

 

Solution:

Create a source qualifier transformation and connect it to the Aggregator transformation.In aggregator transformation create a Output port O_RETURN_VALUE and assign expression SUM(PERSONAL_SALES). Don not specify and group by for any filed and then connect all the other output ports to target and runt the mapping.

In the Aggregator transformation, the ports will be

ID                                            PERSONAL_SALES                                O_RETURN_VALUE=SUM(PERSONAL_SALES)

// Input/Output Port // Input/Output Port // Output Port

 

FLAT FILES

 

 

52.Design a mapping to load the first record from a flat file into one table A, the last record from a flat file into table B and the remaining records into table C?

Step1: You have to assign row numbers to each record. Generate the row numbers using the expression transformation as mentioned above and call the row number generated port as O_count. Create a DUMMY output port in the same expression transformation and assign 1 to that port. So that, the DUMMY output port always return 1 for each row.

In the expression transformation, the ports are
V_count=V_count+1
O_count=V_count
O_dummy=1

The output of expression transformation will be
col, o_count, o_dummy
a, 1, 1
b, 2, 1
c, 3, 1
d, 4, 1
e, 5, 1

Step2: Pass the output of expression transformation to aggregator and do not specify any group by condition. Create an output port O_total_records in the aggregator and assign O_count port to it. The aggregator will return the last row by default. The output of aggregator contains the DUMMY port which has value 1 and O_total_records port which has the value of total number of records in the source.

In the aggregator transformation, the ports are
O_dummy
O_count
O_total_records=O_count

The output of aggregator transformation will be
O_total_records, O_dummy
5, 1

Step3: Pass the output of expression transformation, aggregator transformation to joiner transformation and join on the DUMMY port. In the joiner transformation check the property sorted input, then only you can connect both expression and aggregator to joiner transformation.

In the joiner transformation, the join condition will be
O_dummy (port from aggregator transformation) = O_dummy (port from expression transformation)

The output of joiner transformation will be
col, o_count, o_total_records
a, 1, 5
b, 2, 5
c, 3, 5
d, 4, 5
e, 5, 5

Step 4: In the first group, the condition should be O_count=1 and connect the corresponding output group to table A. In the second group, the condition should be O_count=O_total_records and connect the corresponding output group to table B. The output of default group should be connected to table C.

. Load the header record of the flat file into first target, footer record into second target and the remaining records into the third target.

The solution to this problem I have already posted by using aggregator and joiner. Now we will see how to implement this by reversing the contents of the file.

Solution:

• Connect the source qualifier transformation to the expression transformation. In the expression transformation create the additional ports as mentioned above.
• Connect the expression transformation to a router. In the router transformation create an output group and specify the group condition as o_count=1. Connect this output group to a target and the default group to sorter transformation.
• Sort the data in descending order on o_count port.
• Connect the output of sorter transformation to expression transformation (don’t connect o_count port).
• Again in the expression transformation create the same additional ports mentioned above.
• Connect this expression transformation to router and create an output group. In the output group specify the condition as o_count=1 and connect this group to second target. Connect the default group to the third group.

53. Design a mapping to load the first 3 rows from a flat file into a target?

Solution:

You have to assign row numbers to each record. Generate the row numbers either using the expression transformation as mentioned above or use sequence generator transformation.
Then pass the output to filter transformation and specify the filter condition as O_count <=3

using RANK?

54. Design a mapping to load last 3 rows from a flat file into the target.

Step1: Assign row numbers to each record. Generate row numbers using expression transformation by creating a variable port and incrementing it by 1. After this assign this variable port to output port. After expression transformation, the ports will be as –

Variable_count= Variable_count+1
O_count=Variable_count

Create a dummy output port for same expression transformation and assign 1 to that port. This dummy port will always return 1 for each row.
Finally, the transformation expression will be as follows:

Variable_count= Variable_count+1
O_count=Variable_count
Dummy_output=1

The output of this transformation will be :
Column A O_count Dummy_output
Aanchal 1 1
Priya 2 1
Karishma 3 1
Snehal 4 1
Nupura 5 1

Step 2: Pass the above output to an aggregator and do not specify any group by condition. A new output port should be created as O_total_records in the aggregator and assign O_count port to it. The aggregator will return the last row. The final output of this step will have dummy port with value as 1 and O_total_records will have a total number of records in the source. The aggregator output will be:

O_total_records, Dummy_output
5 1

Step 3: Pass this output to joiner transformation and apply a join on dummy port. The property sorted input should be checked in joiner transformation. Only then the user can connect both expression and aggregator transformation to joiner transformation.

Joiner transformation condition will be as follows:
Dummy_output (port from aggregator transformation) = Dummy_output (port from expression transformation)

The output of joiner transformation will be
Column A o_count o_total_records
Aanchal 1 5
Priya 2 5
Karishma 3 5
Snehal 4 5
Nupura 5 5

Step 4: After the joiner transformation we can send this output to filter transformation and specify filter condition as O_total_records (port from aggregator)-O_count(port from expression) <=2

The filter condition, as a result, will be
O_total_records – O_count <=2

The final output of filter transformation will be :
Column A o_count o_total_records
Karishma 3 5
Snehal 4 5
Nupura 5 5

Load Last N Records of File into Target Table – Informatica

 

Q) How to load only the last N rows from source file into the target table using the mapping in informatica?

 

First take a look at the below data in the source file:

Products
--------
Windows
Linux
Unix
Ubuntu
Fedora
Centos
Debian

I want to load only the last record or footer into the target table. The target should contain only the product “Debain”. Follow the below steps for implementing the mapping logic in informatica: 

• The mapping flow and the transformations are shown below:

SRC->SQ->EXPRESSION->SORTER->EXPRESSION->FILTER->TGT

• Create a new mapping and drag the source into the mapping. By default, it creates the source qualifier transformation. 
• Now create an expression transformation and drag the ports from source qualifier into the expression transformation. In the expression transformation, create the below additional ports and assign the corresponding expressions:

v_count (variable port) = v_count+1
o_count (output port) = v_count

• The output of expression transformation is

Products, o_count
-----------------
Windows, 1
Linux,   2
Unix,    3
Ubuntu,  4
Fedora,  5
Centos,  6
Debian,  7

• Now connect the expression transformation to a sorter transformation and sort the rows on the o_count port in descending order. The output of sorter transformation is shown below:

Products
--------
Debian
Centos
Fedora
Ubuntu
Unix
Linux
Windows

• Create another expression transformation and connect the Products port of sorter to expression transformation. Create the following ports in the expression transformation:

v_count (variable port) = v_count+1
o_count (output port) = v_count

• Connect the expression to a filter transformation and specify the filter condition as o_count = 1. 
• Connect the filter to the target and save the mapping.

 

 

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

 

56. How to load first record and  last record in to target  from a file  using Informatica?

Step 1.

Create  one mapping variable like $$Record_Count and create one Sequence Generator transformation with reset option  then use filter transformation .

In filter transformation put condition like below.

Seq.NEXT  Value =1  or  Seq.NEXT  Value =$$Record_Count

Step 1. Use  Unix script to create/update  parameter file with file record count (wc –l) .this parameter file will supply the value to mapping variable  $$Record_Count

Below is the order of the tasks.

Wf——-àcommand task——-àmain session

Command task—To execute unix script

 

Name
----
A
B
C
D
E
F
G

http://dwhlaureate.blogspot.com/2012/09/4-scenarios-where-we-would-use-stored.html

http://dwhlaureate.blogspot.com/2013/02/informatica-scenarios-based-questions.html

58.Lookup On Multiple Records In Informatica 9

 Need to lookup on File with multiple records and extract records with specific condition (For example in this case only account number starting with “12”)

 Source File

Lookup file

Expected Output

a)In the Lookup condition give the option for Multiple Output for the matching records (this option only available for Informatica 9).The output in Lookup will be as below(Data in Expression)

b)In expression check for the account starting with “12” using below condition

IIF (SUBSTR (Account_number, 1, 1) =’12’, 1, 0)

c)Next step is quite simple. We can use a filter and take records only were the flag is 1.The output will be as below

59.Scenario Implementation 1

60.Scenario Implementation 2

https://forgetcode.com/informatica/943-max-find-maximum-value-of-number-or-date-or-string-port

Convert multiple Rows to single row (multiple Columns) in Informatica

Source Data: Sales table

year product month amount
-------------------------
1999  A      Jan   9600
1999  A      Feb   2000
1999  A      Mar   2500
2001  B      Jan   3000
2001  B      Feb   3500
2001  B      Mar   4000

Target Data:

year product Jan_month Feb_month2 Mar_month
-------------------------------------------
1999   A      9600       2000      2500
2001   B      3000       3500      4000 

Jan_Month (output port) = IIF(month='Jan', amount, null)
Feb_Month (output port) = IIF(month='Feb', amount, null)
Mar_Month (output port) = IIF(month='Mar', amount, null)

o_Jan_Month (output port) = MAX(Jan_Month)
o_Feb_Month (output port) = MAX(Feb_Month)
o_Mar_Month (output port) = MAX(Mar_Month)

61.Describe the approach for the requirement. Suppose the input is:

62.How can we implement aggregation operation without using an Aggregator Transformation in Informatica?

We will use the very basic concept of the Expression Transformation, that at a time we can access  the previous row data as well as the currently processed data in an expression transformation.
What we need is simple Sorter, Expression and Filter transformation to achieve aggregation at Informatica level.
For detailed understanding visit Aggregation without Aggregator.

63.SCENARIO IMPLEMENTATION

64.SCENARIO IMPLEMENTATION

65.SCENARIO IMPLEMENTATION

66.SCENARIO IMPLEMENTATION

67.SCENARIO IMPLEMENTATION

Answer:
Use Expression Transformation to concatenate both values as- name = name1 || name2

https://forgetcode.com/informatica/1814-reg-match-string-contains-or-like-operation

https://informaticareference.wordpress.com/2011/12/14/reg_match/

68.SCENARIO IMPLEMENTATION

69. Scenario Implementation 1
Suppose we have a source table populating two target tables. We connect the NEXTVAL port of the Sequence Generator to the surrogate keys of both the target tables.
Will the Surrogate keys in both the target tables be same? If not how can we flow the same sequence values in both of them.

When we connect the NEXTVAL output port of the Sequence Generator directly to the surrogate key columns of the target tables, the Sequence number will not be the same.
A block of sequence numbers is sent to one target tables surrogate key column. The second target receives a block of sequence numbers from the Sequence Generator transformation only after the first target table receives the block of sequence numbers.
Suppose we have 5 rows coming from the source, so the targets will have the sequence values as TGT1 (1,2,3,4,5) and TGT2 (6,7,8,9,10). [Taken into consideration Start Value 0, Current value 1 and Increment by 1]Now suppose the requirement is like that we need to have the same surrogate keys in both the targets.Then the easiest way to handle the situation is to put an Expression transformation in between the Sequence Generator and the Target tables. The Sequence Generator will pass unique values to the expression transformation, and then the rows are routed from the expression transformation to the targets.

70. Scenario Implementation 2
Suppose we have 100 records coming from the source. Now for a target column population we used a Sequence generator. Suppose the Current Value is 0 and End Value of Sequence generator is set to 80. What will happen?

End Value is the maximum value the Sequence Generator will generate. After it reaches the End value the session fails with the following error message:

TT_11009 Sequence Generator Transformation: Overflow error.
Failing of session can be handled if the Sequence Generator is configured to Cycle through the sequence, i.e. whenever the Integration Service reaches the configured end value for the sequence; it wraps around and starts the cycle again, beginning with the configured Start Value.

71. Scenario Implementation 3
Consider we have two mappings that populate a single target table from two different source systems. Both the mappings have Sequence Generator transform to generate surrogate key in the target table. How can we ensure that the surrogate key generated is consistent and does not generate duplicate values when populating data from two different mappings?discuss

We should use a Reusable Sequence Generator in both the mappings to generate the target surrogate keys.

72. SUPPOSE WE HAVE TWO SOURCE QUALIFIER TRANSFORMATIONS SQ1 AND SQ2 CONNECTED TO TARGET TABLES TGT1 AND TGT2

If we have multiple Source Qualifier transformations connected to multiple targets, we can designate the order in which the Integration Service loads data into the targets.
In the Mapping Designer, We need to configure the Target Load Plan based on the Source Qualifier transformations
in a mapping to specify the required loading order.

It defines the order in which Informatica server loads the data into the targets. This is to avoid integrity constraint
violations

73. SUPPOSE WE HAVE A SOURCE QUALIFIER TRANSFORMATION THAT POPULATES TWO TARGET TABLES. HOW DO YOU ENSURE TGT2 IS LOADED AFTER TGT1? 

In the Workflow Manager, we can Configure Constraint based load ordering for a session. The Integration Service orders the target load on a row-by-row basis. For every row generated by an active source, the Integration Service loads the corresponding transformed row first to the primary key table, then to the foreign key table.
Hence if we have one Source Qualifier transformation that provides data for multiple target tables having primary and foreign key relationships, we will go for Constraint based load ordering.

74. SUPPOSE WE HAVE THE EMP TABLE AS OUR SOURCE. IN THE TARGET WE WANT TO VIEW THOSE EMPLOYEES WHOSE SALARY ARE GREATER THAN OR EQUAL TO THE AVERAGE SALARY FOR THEIR DEPARTMENTS. DESCRIBE YOUR MAPPING APPROACH. 

To start with the mapping we need the following transformations:
After the Source qualifier of the EMP table place a Sorter transformation. Sort based on DEPTNO port.

Next we place a Sorted Aggregator Transformation. Here we will find out the AVERAGE SALARY for each(GROUP BY) DEPTNO.
When we perform this aggregation, we lose the data for individual employees.
To maintain employee data, we must pass a branch of the pipeline to the Aggregator Transformation and pass a branch with the same sorted source data to the Joiner transformation to maintain the original data.
When we join both branches of the pipeline, we join the aggregated data with the original data

So next we need Sorted Joiner Transformation to join the sorted aggregated data with the original data,based on DEPTNO. Here we will be taking the aggregated pipeline as the Master and original dataflow as Detail
Pipeline.

After that we need a Filter Transformation to filter out the employees having salary less than average salary
for their department.
Filter Condition: SAL >= AVG_SAL

Finally we place the Target table instance.

75. SCENARIO IMPLEMENTATION 1 

75. HOW CAN WE LOAD ‘X’ RECORDS (USER DEFINED RECORD NUMBERS) OUT OF ‘N’ RECORDS FROM SOURCE DYNAMICALLY,WITHOUT USING FILTER AND SEQUENCE GENERATOR TRANSFORMATION? 

• Take a mapping parameter say $$CNT to pass the number of records we want to load dynamically by changing in the parameter file each time before session run.
• Next after the Source Qualifier use an Expression transformation and create one output port say CNTR with value CUME (1).
•  Next use an Update Strategy with condition IIF ($$CNT >= CNTR, DD_INSERT, DD_REJECT).

76. SUPPOSE WE HAVE A FLAT FILE WHICH HAS A HEADER RECORD WITH ‘FILE CREATION DATE’, AND DETAILED DATA RECORDS.
DESCRIBE THE APPROACH TO LOAD THE ‘FILE CREATION DATE’ COLUMN ALONG WITH EACH AND EVERY DETAILED RECORD. 

77. SCENARIO IMPLEMENTATION 2 

78. SUPPOSE WE HAVE A FLAT FILE WHICH CONTAINS JUST A NUMERIC VALUE. WE NEED TO POPULATE THIS VALUE IN ONE COLUMN OF THE TARGET TABLE FOR EVERY SOURCE RECORD. HOW CAN WE ACHIEVE THIS? 

79. HOW WILL YOU LOAD A SOURCE FLAT FILE INTO A STAGING TABLE WHEN THE FILE NAME IS NOT FIXED? THE FILE NAME IS LIKE SALES_2013_02_22.TXT, I.E. DATE IS APPENDED AT THE END OF THE FILE AS A PART OF FILE NAME. 

80. SOLVE THE BELOW SCENARIO USING INFORMATICA AND DATABASE SQL. 

81. SCENARIO IMPLEMENTATION 3 

82. Generate different flat file target based on the Locationname, like separate files for Mumbai.dat, Bangalore.dat, and Delhi.dat

Source Table:

1. Sort the source table by the column Location using a Sorter.
2. Create Expression transformation and create the below ports

In_Location = location
V_flag = IIF(In_Location = V_ Location, 0,1)
V_ Location = In_Location
Output_flag (output port) = V_flag

Output_file_name (output port) = location ||’.dat’

****This expression will check when Location changes (Eg.fromMumbai to Bangalore).

3. Now we need to connect the Expression Transformation toTransactionControl transformation. Informatica Power Centre allows us to control the roll back and commit on transaction based on set of rows that passes through theTransactionControl transformation. This allows to define your transaction whether it should be committed or rollback based on the rows that pass through, such as based on the Entry Date or some other column.

Use the following syntax for the expression:

IIF (condition, value1, value2)

IIF(Output_flag = 1, TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)

****This expression will write to the output file when location changes (Eg.from Mumbai to Bangalore)

4. Connect to the Target Table

Note: We have to use special port called “Filename” port in the Target file definition.

84.Load Source File Name in Target – Informatica

 

We will create a simple pass through mapping to load the data and “file name” from a flat file into the target. Assume that we have a source file “customers” and want to load this data into the target “customers_tgt”. The structures of source and target are

Source file name: customers.dat
Customer_Id
Location

Target: Customers_TBL
Customer_Id
Location
FileName

The steps involved are:

• Login to the powercenter mapping designer and go to the source analyzer.
• You can create the flat file or import the flat file.
• Once you created a flat file, edit the source and go to the properties tab. Check the option “Add Currently Processed Flat File Name Port”. This option is shown in the below image.

• A new port, “CurrentlyProcessedFileName” is created in the ports tab.
• Now go to the Target Designer or Warehouse Designer and create or import the target definition. Create a “Filename” port in the target.
• Go to the Mapping designer tab and create new mapping.
• Drag the source and target into the mapping. Connect the appropriate ports of source qualifier transformation to the target.
• Now create a workflow and session. Edit the session and enter the appropriate values for source and target connections.
• The mapping flow is shown in the below image

The loading of the filename works for both Direct and Indirect Source filetype. After running the workflow, the data and the filename will be loaded in to the target. The important point to note is the complete path of the file will be loaded into the target. This means that the directory path and the filename will be loaded(example: /informatica/9.1/SrcFiles/Customers.dat).

If you don’t want the directory path and just want the filename to be loaded in to the target, then follow the below steps:

• Create an expression transformation and drag the ports of source qualifier transformation into it.
• Edit the expression transformation, go to the ports tab, create an output port and assign the below expression to it.

REVERSE
(
  SUBSTR
  (
     REVERSE(CurrentlyProcessedFileName),
     1,
     INSTR(REVERSE(CurrentlyProcessedFileName), '/') - 1
  )
)

• Now connect the appropriate ports of expression transformation to the target definition.

85.How do we achieve DENSE_RANK in Informatica?
In order to achieve the DENSE RANK functionality in Informatica we will use the combination of Sorter, Expression and Filter transformation. Based on the previous example data set, let’s say we want to get the top 2 highest salary of each department as per DENSE RANK.

•  Use a SORTER transformation.
• DEPTNO ASC, SAL DESC
  After the sorter place an EXPRESSION transformation.

86. Suppose we have ten source flat files of same structure. How can we load all the files in target database in a single batch run using a single mapping?
Answer:
After we create a mapping to load data in target database from source flat file definition, next we move on
to the session property of the Source Qualifier.
To load a set of source files we need to create a file say final.txt containing the source flat file names, ten files in our case and set the Source filetype option as Indirect. Next point this flat file final.txt, fully qualified
with Source file directory and Source filename

87. CAN WE PASS THE VALUE OF A MAPPING VARIABLE BETWEEN 2 PIPELINES UNDER THE SAME MAPPING? IF NOT HOW CAN WE ACHIEVE THIS? 

88.How to create Target Files Dynamically.

OR

(How to load all Employees data based upon Deptno in different target file through single Target Instance and Single Mapping.)

Ex: If Deptno=10 then create Target file as DEPT10.txt

       If Deptno=20 then create Target file as DEPT20.txt

       If Deptno=30 then create Target file as DEPT30.txt

http://informaticachamp.blogspot.in/2014/03/scenario-4-how-to-create-target-files.html

Dynamic Target Flat File Name Generation in Informatica

• Create a new file for every session run
• create a new file for each transaction.

Informatica provides a special port,”FileName” in the Target file definition. This port you have to add explicitly. See the below diagram for adding the “FileName” port.

Go to the Target Designer or Warehouse builder and edit the file definition. You have to click on the button indicated in red color circle to add the special port.

Now we will see some informatica mapping examples for creating the target file name dynamically and load the data.

1. Generate a new file for every session run.

Whenever the session runs you need to create a new file dynamically and load the source data into that file. To do this just follow the below steps:

STEP1: Connect the source qualifier to an expression transformation. In the expression transformation create an output port (call it as File_Name) and assign the expression as ‘EMP_’||to_char(sessstarttime, ‘YYYYMMDDHH24MISS’)||’.dat’

STPE2: Now connect the expression transformation to the target and connect eh File_Name port of expression transformation to the FileName port of the target file definition.

STEP3: Create a workflow and run the workflow.

Here I have used sessstarttime, as it is constant throughout the session run. If you have used sysdate, a new file will be created whenever a new transaction occurs in the session run.

The target file names created would look like EMP_20120101125040.dat.

2. Create a new file for every session run. The file name should contain suffix as numbers (EMP_n.dat)

In the above mapping scenario, the target flat file name contains the suffix as ‘timestamp.dat’. Here we have to create the suffix as a number. So, the file names should looks as EMP_1.dat, EMP_2.dat and so on. Follow the below steps:

STPE1: Go the mappings parameters and variables -> Create a new variable, $$COUNT_VAR and its data type should be Integer

STPE2: Connect the source Qualifier to the expression transformation. In the expression transformation create the following new ports and assign the expressions.

v_count (variable port) = v_count+1
v_file_count (variable port) = IIF(v_count = 1, SETVARIABLE($$COUNT_VAR,$$COUNT_VAR+1),$$COUNT_VAR)
o_file_name (output port) = 'EMP_'||v_file_count||'.dat'

STEP3: Now connect the expression transformation to the target and connect the o_file_name port of expression transformation to the FileName port of the target.

3. Create a new file once a day.

You can create a new file only once in a day and can run the session multiple times in the day to load the data. You can either overwrite the file or append the new data.

This is similar to the first problem. Just change the expression in expression transformation to ‘EMP_’||to_char(sessstarttime, ‘YYYYMMDD’)||’.dat’. To avoid overwriting the file, use Append If Exists option in the session properties.

4. Create a flat file based on the values in a port.

You can create a new file for each distinct values in a port. As an example consider the employees table as the source. I want to create a file for each department id and load the appropriate data into the files.

STEP1: Sort the data on department_id. You can either use the source qualifier or sorter transformation to sort the data.

STEP2: Connect to the expression transformation. In the expression transformation create the below ports and assign expressions.

v_curr_dept_id (variable port) = dept_id
v_flag (variable port) = IIF(v_curr_dept_id=v_prev_dept_id,0,1)
v_prev_dept_id (variable port) = dept_id
o_flag (output port) = v_flag
o_file_name (output port) = dept_id||'.dat'

STEP4: Now connect the expression transformation to the transaction control transformation and specify the transaction control condition as

IIF(o_flag = 1, TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)

STEP5: Now connect to the target file definition.

 

Informatica 8.x or later versions provides a feature for generating the target files dynamically. This feature allows you to

89.Produce files as target with dynamic names

95: How to Load only First and Last record of Source to Target.

http://informaticachamp.blogspot.in/2014/03/scenario-6-how-to-load-only-first-and.html

96: How to handle comma in a comma delimited file?

http://informaticachamp.blogspot.in/2014/03/scenario-16-how-to-handle-comma-in.html

97: How to achieve target below?

(Source)

ID  | COUNTRY

101 | INDIA

102 | NEPAL

101 | AMERICA

103 | AFRICA

102 | JAPAN

103 | CHINA

(Target)

SID|ID|COUNTRY

1|101| INDIA

2|101| AMERICA

1|102| NEPAL

2|102| JAPAN

1|103| AFRICA

2|103| CHINA

http://informaticachamp.blogspot.in/2014/03/scenario-10-how-to-assign-unique-id-for.html

98.How can we implement Reverse Pivoting using Informatica transformations?

Pivoting can be done using Normalizer transformation. For reverse-pivoting we will need to use an aggregator transformation like below:

 

99. SUPPOSE WE HAVE A COLUMN IN SOURCE WITH VALUES AS BELOW: 

 

99. SCENARIO IMPLEMENTATION 4 

 

 

100.Generate rows based on a column value – Informatica

I have the products table as the source and the data of the products table is shown below.

Table Name: Products
Product  Quantity
-----------------
Samsung  NULL
Iphone   3
LG       0
Nokia    4

Now i want to duplicate or repeat each product in the source table as many times as the value in the quantity column. The output is

product  Quantity
----------------
Iphone   3
Iphone   3
Iphone   3
Nokia    4
Nokia    4
Nokia    4
Nokia    4

The Samsung and LG products should not be loaded as their quantity is NULL, 0 respectively.

Now create informatica workflow to load the data in to the target table?

Solution:

Follow the below steps

• Create a new mapping in the mapping designer
• Drag the source definition in to the mapping
• Create the java transformation in active mode
• Drag the ports of source qualifier transformation in to the java transformation.
• Now edit the java transformation by double clicking on the title bar of the java transformation and go to the “Java Code” tab.
• Enter the below java code in the “Java Code” tab.

if (!isNull("quantity"))
{
  double cnt = quantity;
  for (int i = 1; i <= quantity; i++)
  {
    product = product;
    quantity = quantity;
    generateRow();
  }
}

• Now compile the java code. The compile button is shown in red circle in the image.
• Connect the ports of the java transformation to the target.
• Save the mapping, create a workflow and run the workflow.

If you like this post, please share it on google by clicking on the +1 button.

 

 

 

How to generate or load values in to the target table based on a column value using informatica etl tool

101.Informatica Scenarios:Pivoting of records(Pivoting of Employees by Department)

In this scenario we will discuss about how to pivot a set of records based on column:

Source Records:

Dept_id	Emp_name
10	CLARK
10	KING
10	MILLER
20	JONES
20	FORD
20	ADAMS
20	SMITH
20	SCOTT
30	WARD
30	TURNER
30	ALLEN
30	BLAKE
30	MARTIN
30	JAMES

Expected Output

DEPT_ID	EMP_NAME
10	CLARK|KING|MILLER
20	JONES|FORD|ADAMS|SMITH|SCOTT
30	WARD|TURNER|ALLEN|JAMES|BLAKE|MARTIN

For this we can use the below pivot_mapping. To get the required source records we have used the below source query.

select  d.deptno,e.ename from emp e,dept d where

e.deptno=d.deptno

Sort: Next step is to sort the Deptid using a Sorter or we can sort using the query directly.

In the expression we need to use the logic as shown below and arrange the columns in the below order

V_EMP_NAME = DECODE(V_DEPT_PREV,DEPT_ID,V_DEPT||’|’||EMP_NAME, EMP_NAME)

O_EMP_NAME=V_EMP_NAME

V_DEPT_PREV=DEPT_ID

Group by dept_id: To group the deptid column we can make use of the AGGREGATOR .

When we group by using the DEPTID it will group and return the last row of each department group and the target table will have the below records as expected

SQL> SELECT * FROM PIVOT_TARGET;

DEPTN        ENAME

10       CLARK|KING|MILLER

20        JONES|FORD|ADAMS|SMITH|SCOTT

30        WARD|TURNER|ALLEN|JAMES|BLAKE|MARTIN

 

102.have a file which contains 2 columns like below:

col1   col2

1         A

2         Z

3         K

15       PG

49       KMC

many records like this and we don’t know how many records are there and at what point they are ending.

We just know that 1st column is Integer number and second is string.Our requirement is to repeat the second column (ie., string value) as many times as mentioned in column 1.

Which means our result should be like this:

col1  col2

1        A

2        ZZ

3        KKK

15      PGPGPGPG…….15 times

49      KMCKMCKMC…….49 times

JAVA transformation with cycle for (i=0; i<=col1; i++) Out_port=Out_port+ col2;

Not built into the tool. An EXP won’t help because the EXP is a passive transformation whereas for this task you need some active transformation logic.

A Normalizer can be used if (and only if) the maximum number of potential repetitions is known in advance. For example, if you know that the first column in this file can never exceed a value of e.g. 50, then you can do it using an EXP, a Normalizer, and a Filter transformation.

 

104.Reverse the Contents of Flat File – Informatica

Informatica Enterprise Solution
Informatica Power center
Informatica Power exchange
Informatica Data quality

Informatica Data quality
Informatica Power exchange
Informatica Power center
Informatica Enterprise Solution

Variable port: v_count = v_count+1
Output port o_count = v_count

https://forgetcode.com/Informatica/1057-Decode-Value-Search-or-Nested-If-Else

107.SCENARIO IMPLEMENTATION

108. SCENARIO IMPLEMENTATION 1 

109. SCENARIO IMPLEMENTATION 2 

111: How to Implement SCD2 with Dynamic Lookup?

http://informaticachamp.blogspot.in/2014/03/scenario-8-how-to-implement-scd2-with.html

 

While Implementing SCD2 with Dynamic Lookup the challenge is to fetch only unique records from lookup table to cache because Dynamic Lookup Cache doesn’t support duplicate value and session will fail if any duplicate records is trying to insert into Lookup Cache. (Challenge 1)

 

112: Implementing SCD1 using MD5.

http://informaticachamp.blogspot.in/2014/03/scenario-10-implementing-scd1-using-md5.html

 

 

113: How to Implement Hybrid SCD or SCD Type 6

http://informaticachamp.blogspot.in/2014/03/scenario-15-how-to-implement-hybrid-scd.html

 

 

114: How to implement SCD1 along with delete. (Insert, Update, Delete)

http://informaticachamp.blogspot.in/2014/06/scenario-22-how-to-implement-scd1-along.html