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.
- 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.
- 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.
- 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.
- 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 |
- Drag the source to mapping and connect it to an aggregator transformation.
- In aggregator transformation, group by the key column and add a new port. Call it count_rec to count the key column.
- 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
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 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.
Product | O_Count |
A | 1 |
B | 1 |
B | 2 |
B | 3 |
C | 1 |
C | 2 |
D | 1 |
B
C
C
B
D
B
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.
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.
- Drag the source and connect to an expression transformation.
- Add the next value of a sequence generator to expression transformation.
- In expression transformation make two port, one is “odd” and another “even”.
- Write the expression as below
- Connect a router transformation to expression.
- Make two group in router.
- Give condition as below
- 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.
- Connect an expression transformation after source qualifier.
- Add the next value port of sequence generator to expression transformation.
- In expression create a new port (validate) and write the expression as in the picture below.
- Connect a filter transformation to expression and write the condition in property as given in the picture below.
- 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:
- Design the mapping just like an ‘INSERT’ only mapping, without Lookup, Update Strategy Transformation.
- First set Treat Source Rows As property as shown in below image.
- 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
- Connect one update strategy transformation next to SQF.
- In update strategy properties write the condition like this
- 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
- connect out-puts from SQF to Update Strategy transformation.
- In properties of Update Strategy write the condition like this
- Connect the Update Strategy to target
SCD Type1 Mapping
The SCD Type 1 methodology overwrites old data with new data, and therefore does not need to track historical data.
- Here is the source.
- We will compare the historical data based on key column CUSTOMER_ID.
- This is the entire mapping:
- Connect lookup to source. In Lookup fetch the data from target table and send only CUSTOMER_ID port from source to lookup.
- Give the lookup condition like this:
- Then, send rest of the columns from source to one router transformation.
- In router create two groups and give condition like this:
- 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.
- Change_rec group of router bring to one update strategy and give the condition like this:
- 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.
- To identifying new_rec we should and one new_pm? and one vesion_no.
- This is the source:
- This is the entire mapping:
- 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.
- 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 |
- Create 3 expression transformations exp_1,exp_2 and exp_3 with 1 port each.
- Connect col1 from Source Qualifier to port in exp_1.
- Connect col2 from Source Qualifier to port in exp_2.
- Connect col3 from source qualifier to port in exp_3.
- Make 3 instances of the target. Connect port from exp_1 to target_1.
- Connect port from exp_2 to target_2 and connect port from exp_3 to target_3.
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.
- Drag all the sources in to the mapping designer.
- Add one union transformation and configure it as follows.Group Tab.
Group Ports Tab.
- Connect the sources with the three input groups of the union transformation.
- 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.
- Bring three sources into the mapping designer.
- Create the Joiner -1 to join Employees and Departments using Department_ID.
- 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.
- 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.
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.
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
- Connect SQF with an expression transformation.
- 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.
- 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/1056-trunc-truncate-day-or-month-or-year-part-in-datetime use?
- Connect all the rows from SQF to update strategy transformation.
- In update strategy properties write the expression like this
- 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
- Connect one expression transformation next to SQF.
- In expression create one out/put port “diff” and make it integer type.
- 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
- Connect the ports from SQF to an expression transformation.
- In expression create another oupput port hire_date1 and make it to date data-type, shown in picture.
- In Hire_date1 write the condition like this.
- 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
- Drag the source and connect to an expression transformation
- In Expression create two ports one is name1(as variable port) and Middle_Name (o/p port)
- In Name1 write the condition like this
- In Middle_Name write the condition like this
- 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
- Drag the source to mapping area and connect with an expression trans formation as shown bellow.
- In expression transformation create two output port one is f_name and other is l_name.
- In f_name write the condition like this
- 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
- Connect SQF to an expression.
- In expression make hire_date as input only and make another port hire_date1 as o/p port with date data type.
- In o/p port of hire_date write condition like as below
- 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
- Connect 0/p columns of SQF to an expression transformation.
- 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
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
- Connect required columns from SQF to an expression transformation.
- 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)) - 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
- Drag the source and connect it to expression transformation
- In expression make a output port sal1 and make sal as input port only.
- In sal1 write the condition as like bellow
- 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.
- Put the source to mapping and connect it to an expression transformation.
- Drag an sequence generator transformation and set properties like this And connect the next value port to expression.
- Drag all output port of expression to router. In router make three groups and gve the conditions Like this
- 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.
- 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
- Then drag the source to mapping area and connect to an expression transformation.
- In expression create a output port as sal1 and make sal as input only as bellow.
- 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 . 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
- Drag the source to mapping area and connect each port to an expression transformation.
- In expression transformation add a new col sal1 and make it as out put and sal as in put only as shown in picture.
- In expression write the condition like this.
- 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:
- 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.
- 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).
- In filter set deptno=$$v1 (that means only dept no 20 record will go to the target.)
- 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:
- In repository go to menu “tool” then “queries”. Query Browser dialog box will appear.Then click on new button.
- In Query Editor, choose folder name and object type as I have shown in the picture.
- After that, execute it (by clicking the blue arrow button).
- 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 |
- Pull the source to mapping and then connect it to expression.
- 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:
- Drag the source and connect to an expression.Connect the next value port of sequence generator to expression.
- Send the all ports to a router and make three groups as bellow
Group1Group2Group3 - mod(NEXTVAL,30) >= 21 and mod(NEXTVAL,30) <= 29 or mod(NEXTVAL,30) = 0
- mod(NEXTVAL,30) >= 11 and mod(NEXTVAL,30) <= 20
- mod(NEXTVAL,30) >= 1 and mod(NEXTVAL,30) <= 10
- 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
- Put the source to mapping and connect the ports to aggregator transformation.
- 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. - Then send it to expression transformation.
- In expression make four output ports (dept10, dept20, dept30, dept40) to validate dept no
And provide the expression like in the picture below. - Then connect to router transformation. And create a group and fill condition like below.
- 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:
- Drag the source to mapping and connect it to sorter transformation.
- Arrange the salary in descending order in sorter as follows and send the record to expression.
sorter properties
- Add the next value of sequence generator to expression.(start the value from 1 in sequence generator).
sorter to exp mapping
- Connect the expression transformation to a filter or router. In the property set the condition as follows-
- 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:
- Drag and drop the source to mapping.
- Step:2 In source-Qualifier , go to property and write the SQL query like1
select
*
from
emp
where
rownum <= (
select
count
(*)/2
from
emp)
- Then connect to target.Now you are ready to run the mapping to see it in action.
- Drag and drop the source to mapping.
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.
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
ID | PERSONAL_SALES |
10 | 40000 |
20 | 80000 |
30 | 40000 |
40 | 60000 |
50 | NULL |
60 | 50000 |
.Cumulative Sum Calculation 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.
ID | PERSONAL_SALES | O_RETURN_VALUE |
10 | 40000 | 40000 |
20 | 80000 | 120000 |
30 | 40000 | 160000 |
40 | 60000 | 220000 |
50 | NULL | 220000 |
60 | 50000 | 270000 |
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 |
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 |
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.
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:
ID PERSONAL_SALES V_COUNT=V_COUNT+1 O_COUNT=V_COUNT | // Input/Output Port // Input/Output Port // Variable Port // Output Port |
ID | PERSONAL_SALES | O_COUNT |
10 | 40000 | 1 |
20 | 80000 | 2 |
30 | 40000 | 3 |
40 | 60000 | 4 |
50 | NULL | 5 |
ID PERSONAL_SALES V_COUNT=V_COUNT+1 O_COUNT=V_COUNT-1 | // Input/Output Port // Input/Output Port // Variable Port // Output Port |
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.
ID | PERSONAL_SALES | O_RETURN_VALUE |
10 | 40000 | NULL |
20 | 80000 | 120000 |
30 | 40000 | 120000 |
40 | 60000 | 100000 |
50 | NULL | 60000 |
60 | 50000 | 50000 |
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 |
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:
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
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.
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
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
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
57.Load all records except last N – Informatica
Name ---- A B C D E F G
After excluding the last 5 records, i want to load A,B into the target. How to implement a mapping logic for this in informatica?
Solution: Follow the below steps
- Connect the source qualifier transformation, NEXTVAL port of sequence generator to the sorter transformation.
- In the sorter transformation, check the key box corresponding to NEXTVAL port and change the direction to Descending.
- Create one more sequence generator transformation and a filter transformation.
- Connect the NEXTVAL port of the second sequence generator transformation to the filter and Name port of sorter transformation to filter.
- Specify the filter condition as NEXTVAL > 5.
- Save the mapping. Create a workflow and session. Save the workflow and run the workflow.
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
ID | User_Name |
1 | James |
2 | Williams |
3 | Ravi |
Lookup file
ID | Account_Name | Account_number |
1 | Acc _James_1 | 123232 |
1 | Acc _James_2 | 45778266 |
2 | Acc _ Williams_1 | 5455546 |
2 | Acc _ Williams_2 | 1234343 |
3 | Acc _ Ravi_1 | 254589 |
3 | Acc _ Ravi_2 | 12544456 |
Expected Output
ID | Account_Name | Account_number | User_Name |
1 | Acc _James_1 | 123232 | James |
2 | Acc _ Williams_2 | 1234343 | Williams |
3 | Acc _ Ravi_2 | 12544456 | Ravi |
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)
ID | Account_Name | Account_number | User_Name |
1 | Acc _James_1 | 123232 | James |
1 | Acc _James_2 | 45778266 | James |
2 | Acc _ Williams_1 | 5455546 | Williams |
2 | Acc _ Williams_2 | 1234343 | Williams |
3 | Acc _ Ravi_1 | 254589 | Ravi |
3 | Acc _ Ravi_2 | 12544456 | Ravi |
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
ID | Account_Name | Account_number | User_Name |
1 | Acc _James_1 | 123232 | James |
2 | Acc_ Williams_2 | 1234343 | Williams |
3 | Acc _ Ravi_2 | 12544456 | Ravi |
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
I have the sales table as a source. The sales table contains the sales information of products for each year and month. The data in the source table is shown below:
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
The sales information of a product for each month is available in a separate row. I want to convert the rows for all the months in a specific year to a single row. The output is shown below:
Target Data: year product Jan_month Feb_month2 Mar_month ------------------------------------------- 1999 A 9600 2000 2500 2001 B 3000 3500 4000
How to implement a mapping logic for this in informatica?
Solution:
Follow the below steps to implement the mapping logic for the above scenario in informatica:
- Create a new mapping.
- Drag the source into the mapping.
- Create an expression transformation.
- Drag the ports of source qualifier into the expression transformation.
- Create the below additional ports in the expression transformation and assign the corresponding expressions:
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)
- Connect the expression transformation to an aggregator transformation. Connect only the ports year, product, Jan_Month, Feb_Month,Mar_Month ports of expression to aggregator transformation. Group by on year and product in aggregator transformation.
- Create the below additional ports in aggregator transformation and assign the corresponding expressions:
o_Jan_Month (output port) = MAX(Jan_Month) o_Feb_Month (output port) = MAX(Feb_Month) o_Mar_Month (output port) = MAX(Mar_Month)
- Now connect the ports year, product, o_Jan_Month, o_Feb_Month, o_Mar_Month of aggregator transformation to the target.
- Save the mapping.
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:
Dept name | Dept ID | Location |
DWH | 1 | Mumbai |
Java | 2 | Bangalore |
Dot net | 3 | Delhi |
- Sort the source table by the column Location using a Sorter.
- 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).
- 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)
- 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.
89.Produce files as target with dynamic names
Scenario:How to generate file name dynamically with name of sys date ?
Solution:
- Drag your target file to target designer and add a column as show on the picture. It’s not a normal column .click on the ‘add file name to the table’ property. (I have given a red mark there)
- Then drag your source to mapping area and connect it to an expression transformation.
- In expression transformation add a new port as string data type and make it output port.
- In that output port write the condition like describe as bellow and then map it in to filename port of target. Also send other ports to target. Finally run the session. You will find two file one with sys date and other one is ‘.out’ file which one you can delete.
90. Design a mapping to load a target table with the following values from the above source?
department_no, employee_list
10, A
10, A,B
10, A,B,C
10, A,B,C,D
20, A,B,C,D,P
20, A,B,C,D,P,Q
20, A,B,C,D,P,Q,R
20, A,B,C,D,P,Q,R,S
Solution:
Step1: Use a sorter transformation and sort the data using the sort key as department_no and then pass the output to the expression transformation. In the expression transformation, the ports will be
department_no
employee_Name
V_employee_list =IF(ISNULL(V_employee_list),employee_name,V_employee_list||’,’||employee_name)
O_employee_list = V_employee_list
Step2: Now connect the expression transformation to a target table.
91.Design a mapping to load a target table with the following values from the above source?
department_no, employee_list
10, A
10, A,B
10, A,B,C
10, A,B,C,D
20, P
20, P,Q
20, P,Q,R
20, P,Q,R,S
Solution:
Step1: Use a sorter transformation and sort the data using the sort key as department_no and then pass the output to the expression transformation. In the expression transformation, the ports will be
department_no
employee_name
V_curr_deptno=department_no
V_employee_list = IIF(V_curr_deptno! = V_prev_deptno,employee_name,V_employee_list||’,’||employee_name)
V_prev_deptno=department_no
O_employee_list = V_employee_list
Step2: Now connect the expression transformation to a target table.
92.Design a mapping to load a target table with the following values from the above source?
department_no, employee_names
10, A,B,C,D
20, P,Q,R,S
Solution:
The first step is same as the above problem. Pass the output of expression to an aggregator transformation and specify the group by as department_no. Now connect the aggregator transformation to a target table
93. Consider the following product types data as the source.
Product_id, product_type
10, video
10, Audio
20, Audio
30, Audio
40, Audio
50, Audio
10, Movie
20, Movie
30, Movie
40, Movie
50, Movie
60, Movie
Assume that there are only 3 product types are available in the source. The source contains 12 records and you dont know how many products are available in each product type.
. Design a mapping to select 9 products in such a way that 3 products should be selected from video, 3 products should be selected from Audio and the remaining 3 products should be selected from Movie.
Solution:
Step1: Use sorter transformation and sort the data using the key as product_type.
Step2: Connect the sorter transformation to an expression transformation. In the expression transformation, the ports will be
product_id
product_type
V_curr_prod_type=product_type
V_count = IIF(V_curr_prod_type = V_prev_prod_type,V_count+1,1)
V_prev_prod_type=product_type
O_count=V_count
Step3: Now connect the expression transformation to a filter transformation and specify the filter condition as O_count<=3. Pass the output of filter to a target table.
. In the above problem Q1, if the number of products in a particular product type are less than 3, then you wont get the total 9 records in the target table. For example, see the videos type in the source data. Now design a mapping in such way that even if the number of products in a particular product type are less than 3, then you have to get those less number of records from another porduct types. For example: If the number of products in videos are 1, then the remaining 2 records should come from audios or movies. So, the total number of records in the target table should always be 9.
Solution:
The first two steps are same as above.
Step3: Connect the expression transformation to a sorter transformation and sort the data using the key as O_count. The ports in soter transformation will be
product_id
product_type
O_count (sort key)
Step 4: Discard O_count port and connect the sorter transformation to an expression transformation. The ports in expression transformation will be
product_id
product_type
V_count=V_count+1
O_prod_count=V_count
Step 5: Connect the expression to a filter transformation and specify the filter condition as O_prod_count<=9. Connect the filter transformation to a target table.
We can process all flat files through one mapping and one session using list file.
First we need to create list file using unix script for all flat file the extension of the list file is .LST.
This list file it will have only flat file names.
At session level we need to set
source file directory as list file path
And source file name as list file name
And file type as indirect.
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.
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.
The source data looks like
col1, col2, col3
a, b, c
d, e, f
The target table data should look like
Col
a
b
c
d
e
f
Solution:
Create three expression transformations with one port each. Connect col1 from Source Qualifier to port in first expression transformation. Connect col2 from Source Qualifier to port in second expression transformation. Connect col3 from source qualifier to port in third expression transformation. Create a union transformation with three input groups and each input group should have one port. Now connect the expression transformations to the input groups and connect the union transformation to the target table.
104.Reverse the Contents of Flat File – Informatica
As an example consider the source flat file data as
Informatica Enterprise Solution Informatica Power center Informatica Power exchange Informatica Data quality
The target flat file data should look as
Informatica Data quality Informatica Power exchange Informatica Power center Informatica Enterprise Solution
Solution:
Follow the below steps for creating the mapping logic
- Create a new mapping.
- Drag the flat file source into the mapping.
- Create an expression transformation and drag the ports of source qualifier transformation into the expression transformation.
- Create the below additional ports in the expression transformation and assign the corresponding expressions
Variable port: v_count = v_count+1 Output port o_count = v_count
- Now create a sorter transformation and drag the ports of expression transformation into it.
- In the sorter transformation specify the sort key as o_count and sort order as DESCENDING.
- Drag the target definition into the mapping and connect the ports of sorter transformation to the target.
105.Concatenation of duplicate value by comma separation
Scenario: You have two columns in source table T1, in which the col2 may contain duplicate values.All the duplicate values in col2 of will be transformed as comma separated in the column col2 of target table T2.
Source Table: T1
Col1 | Col2 |
---|---|
a | x |
b | y |
c | z |
a | m |
b | n |
Target Table: T2
col1 | col2 |
---|---|
a | x,m |
b | y,n |
c | z |
Solution:
https://forgetcode.com/Informatica/1057-Decode-Value-Search-or-Nested-If-Else
- We have to use the following transformation as below.
First connect a sorter transformation to source and make col1 as key and its order is ascending. After that connect it to an expression transformation. - In Expression make four new port and give them name as in picture below.
- In concat_val write expression like as describe bellow and send it to an aggregator
- In aggregator group it by col1 and send it to target
- Finally run the session.
106.Create a workflow to load only the Fibonacci numbers in the target table. The target table data should look like as
Id
1
2
3
5
8
13
…..
In Fibonacci series each subsequent number is the sum of previous two numbers. Here assume that the first two numbers of the Fibonacci series are 1 and 2.
Solution:
STEP1: Drag the source to the mapping designer and then in the Source Qualifier Transformation properties, set the number of sorted ports to one. This will sort the source data in ascending order. So that we will get the numbers in sequence as 1, 2, 3, ….1000
STEP2: Connect the Source Qualifier Transformation to the Expression Transformation. In the Expression Transformation, create three variable ports and one output port. Assign the expressions to the ports as shown below.
Ports in Expression Transformation:
id
v_sum = v_prev_val1 + v_prev_val2
v_prev_val1 = IIF(id=1 or id=2,1, IIF(v_sum = id, v_prev_val2, v_prev_val1) )
v_prev_val2 = IIF(id=1 or id =2, 2, IIF(v_sum=id, v_sum, v_prev_val2) )
o_flag = IIF(id=1 or id=2,1, IIF( v_sum=id,1,0) )
STEP3: Now connect the Expression Transformation to the Filter Transformation and specify the Filter Condition as o_flag=1
STEP4: Connect the Filter Transformation to the Target Table.
107.SCENARIO IMPLEMENTATION
108. SCENARIO IMPLEMENTATION 1
109. SCENARIO IMPLEMENTATION 2
110. Explain SCD TYPE 3 through mapping.
SCD Type3 Mapping
In SCD Type3, there should be two columns added to identifying a single attribute. It stores one time historical data with current data.
- This is the source:
- This is the entire mapping:
- Up to router transformation, all the procedure is same as described in SCD type1.
- The only difference is after router, bring the new_rec to router and give condition dd_insert send to.
- Create one new primary key send to target. For old_rec send to update_strategy and set condition dd_insert and send to target.
- You can create one effective_date column in old_rec table
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
115. The source table contains two columns “id” and “val”. The source data looks like as below
id val
1 a,b,c
2 pq,m,n
3 asz,ro,liqt
Here the “val” column contains comma delimited data and has three fields in that column.
Create a workflow to split the fields in “val” column to separate rows. The output should look like as below.
id val
1 a
1 b
1 c
2 pq
2 m
2 n
3 asz
3 ro
3 liqt
Solution:
STEP1: Connect three Source Qualifier transformations to the Source Definition
STEP2: Now connect all the three Source Qualifier transformations to the Union Transformation. Then connect the Union Transformation to the Sorter Transformation. In the sorter transformation sort the data based on Id port in ascending order.
STEP3: Pass the output of Sorter Transformation to the Expression Transformation. The ports in Expression Transformation are:
id (input/output port)
val (input port)
v_currend_id (variable port) = id
v_count (variable port) = IIF(v_current_id!=v_previous_id,1,v_count+1)
v_previous_id (variable port) = id
o_val (output port) = DECODE(v_count, 1,
SUBSTR(val, 1, INSTR(val,’,’,1,1)-1 ), 2, SUBSTR(val, INSTR(val,’,’,1,1)+1, INSTR(val,’,’,1,2)INSTR(val,’,’,1,1)-1), 3, SUBSTR(val,INSTR(val,’,’,1,2)+1),
NULL
)
STEP4: Now pass the output of Expression Transformation to the Target definition. Connect id, o_val ports of Expression Transformation to the id, val ports of Target Definition.
For those who are interested to solve this problem in oracle sql, Click Here. The oracle sql query provides a dynamic solution where the “val” column can have varying number of fields in each row.
116: How to achieve daily load and weekly load through single workflow?
Ex: If I have two sessions SCD1 and SCD2 in single workflow and I want to run only SCD1 on daily basis and only SCD2 on weekly basis.
http://informaticachamp.blogspot.in/2014/03/scenario-7-how-to-achieve-daily-load.html
117.How to maintain Audit Table for mapping/session/workflow without accessing Repository tables?
http://informaticachamp.blogspot.in/2014/03/scenario-11-how-to-maintain-audit-table.html
118.How to Create Parameter File Dynamically?
http://informaticachamp.blogspot.in/2014/03/scenario-12-how-to-create-parameter.html
119. How to send notification for long running workflow/sessions?
Ex: If particular workflow is running for longer than specified time then we need to send a notification to concern team.
This solution helps Production Support fellows a lot. They escaped from being a watch dog.
http://informaticachamp.blogspot.in/2014/03/scenario-14-how-to-send-notification.html
120: How to restrict total no. of records to load into target based upon session run.
http://informaticachamp.blogspot.in/2014/04/scenario-18-how-to-restrict-total-no-of.html
121.How to populate date dimension through PL/SQL code by using stored procedure transformation.
http://informaticachamp.blogspot.in/2014/05/scenario-19-how-to-populate-date.html
122: How to do Union without using Union Transformation
http://informaticachamp.blogspot.in/2014/06/scenario-21-how-to-do-union-without.html
123: How to Join two tables without using Joiner, Source Qualifier and Lookup.
http://informaticachamp.blogspot.in/2014/06/scenario-23-how-to-join-two-tables.html
124. HOW CAN WE PERFORM CHANGED DATA CAPTURE BASED ON LOAD SEQUENCE NUMBER (INTEGER) COLUMN PRESENT IN THE SOURCE TABLE?
Create a Mapping Variable as integer data type and Aggregation type as MAX. Set the value of this mapping variable in any of these transformations (Expression, Filter, Router or Update Strategy).
Use SETMAXVARIABLE( $$Variable, load_seq_column ) function. This function will assign the MAX sequence number of that particular load into the variable $$variable.
This function executes only if a row is marked as insert. SETMAXVARIABLE ignores all other row types and the current value remains unchanged. The function sets the current value of a mapping variable to the higher of two values- the current value of the variable or the value from the source column for each record. At the end of a successful session, the Integration Service saves the final current value to the repository.
When used with a session that contains multiple partitions, the Integration Service generates different current values for each partition. At the end of the session, it saves the highest current value across all partitions to the repository. Unless overridden, it uses the saved value as the initial value of the variable for the next session run.
Now since the max sequence number for previous load is captured in this mapping variable and is saved in the repository. We can use this variable as a filter in the Source Qualifier query. Next time when we run the workflow, it will only extract those records having load sequence number greater than this sequence number.