ADVANCED SCENARIOS
1.look at the following tree structure diagram. From the tree structure, you can easily derive the
parent-child relationship between the elements. For example, B is parent of D and E.
The above tree structure data is represented in a table as shown below.
c1, c2, c3, c4
A, B, D, H
A, B, D, I
A, B, E, NULL
A, C, F, NULL
A, C, G, NULL
Here in this table, column C1 is parent of column C2, column C2 is parent of column C3, column C3
is parent of column C4.
. Design a mapping to load the target table with the below data. Here you need to generate sequence numbers for each element and then you have to get the parent id. As the element “A” is at root, it does not have any parent and its parent_id is NULL.
id, element, parent_id
1, A, NULL
2, B, 1
3, C, 1
4, D, 2
5, E, 2
6, F, 3
7, G, 3
8, H, 4
9, I, 4
. This is an extension to the problem Q1. Let say column C2 has null for all the rows, then C1 becomes the parent of C3 and c3 is parent of C4. Let say both columns c2 and c3 has null for all the rows. Then c1 becomes the parent of c4. Design a mapping to accommodate these type of null
conditions.
https://www.folkstalk.com/2011/11/oracle-complex-queries-part-3.html
2.How to Concat row data through informatica?
Source:
| Ename | EmpNo |
| stev | 100 |
| methew | 100 |
| john | 101 |
| tom | 101 |
Target:
| Ename | EmpNo |
| Stev methew | 100 |
| John tom | 101 |
Ans:
Using Dynamic Lookup on Target table:
If record doesn’t exit do insert in target .If it is already exist then get corresponding Ename vale from lookup and concat in expression with current Ename value then update the target Ename column using update strategy.
Using Var port Approach:
Sort the data in sq based on EmpNo column then Use expression to store previous record information using Var port after that use router to insert a record if it is first time if it is already inserted then update Ename with concat value of prev name and current name value then update in target.
———————————
3:How to load single source data in four different target based upon session run through single mapping.
Ex: One Source (Emp Table)
1st run of Session–>EMP_TGT1
2nd run of Session–>EMP_TGT2
3rd run of Session–>EMP_TGT3
4th run of Session–>EMP_TGT4
5th run of Session–>EMP_TGT1
http://informaticachamp.blogspot.in/2014/02/scenarios.html
4. Split Total Number of Records in two parts and load it based upon session run.
Ex: Emp table has 14 records. On first time session run 7 records should be loaded and on second time run remaining 7 records should be loaded.
In other words we can say on each run only 7 records should be loaded.
http://informaticachamp.blogspot.in/2014/02/scenario-2-split-total-number-of.html
5.How to load multi-format (Delimited) column Flat File source data into two different targets?
Ex:
cust_id,cname,phone,state_id
101,AMITH,9897045679,201
102,JAGAN,9908907869,203
103,MAHESH,890899089,201
state_id||Sname||Region
201||Karntaka||South
202||Delhi||North
203||Maharashtra||West
http://informaticachamp.blogspot.in/2014/03/scenario-3-how-to-load-multi-format.html
6. How to Convert Column To Row
(Source)
TERM,JAVA,.NET,ORACLE
1st,60,40,70
2nd,50,65,80
(Target)
TERM,JAVA,.NET,ORACLE
1ST,60,null,null
1ST,60,40,null
1ST,60,40,70
2nd,50,null,null
2nd,50,65,null
2nd,50,65,80
http://informaticachamp.blogspot.in/2014/03/scenario-5-how-to-convert-column-to-row.html
7. How to Achieve Target below…
(Source)
ID, STATES
1,BNG,CHE,HYBD
2,PUNE,MUMBAI
3,DELHI
(Target)
1,BNG
1,CHE
1,HYBD
2,PUNE
2,MUMBAI
3,DELHI
http://informaticachamp.blogspot.in/2014/03/scenario-5a-how-to-convert-column-to-row.html
8. How to Achieve Target below…
(Source)
ENAME,ENAME1,ENAME2
John,Mathew,Stev
Parker,Heden,Waugh
(Target)
FNAME,LNAME
John,Parker
Mathew,Heden
Stev,Waugh
http://informaticachamp.blogspot.in/2014/03/scenario-5b-how-to-convert-column-to.html
9.How to Achieve Target below…
(Source)
ID,NAME,LOC
101,null,null
null,SIMITH,null
null,null,USA
102,null,null
null,JOHN,null
null,null,UK
Note: NULL are just for representation. There is no value in that column.
(Target)
ID NAME LOC
101 SIMITH USA
102 JOHN UK
http://informaticachamp.blogspot.in/2014/03/scenario-5c-how-to-convert-column-to.html
10. How to achieve target below…
(Source)
BRAND_ID,PRODUCT,PRICE
100,Cellphone,10000
100,Television,15000
100,Fridge,9000
101,Cellphone,8000
101,Television,12000
102,Cellphone,9000
(Target)
BRAND_ID, PRODUCT, TOTAL_PRICE
100,Cellphone,Television,Fridge,34000
101,Cellphone,Television,20000
102,Cellphone,9000
http://informaticachamp.blogspot.in/2014/03/scenario-13-how-to-convert-row-to-column.html
11. How to achieve target below…
(Source)
SUBJECT,MARKS
JAVA,60
ORACLE,50
INFORMATICA,78
JAVA,45
ORACLE,55
INFORMATICA,52
(Target)
Column1 Column2 Column3
JAVA ORACLE INFORMATICA
60 50 78
JAVA ORACLE INFORMATICA
45 55 52
http://informaticachamp.blogspot.in/2014/03/scenario-13-how-to-convert-row-to-column_16.html
12: How to Convert Row to Column and convert four column data to eight column data.
(Source)
COL1,COL2,COL3,COL4
7369, SMITH, CLERK, 7902
17-DEC-1980, 800, 400, 20
7499, ALLEN, SALESMAN, 7698
20-FEB-1981, 1600, 300, 30
7521, WARD, SALESMAN, 7698
22-FEB-1981, 1250, 500, 30
7566, JONES, MANAGER, 7839
2-APR-1981, 2975, 400, 20
7654, MARTIN, SALESMAN, 7698
28-SEP-1981, 1250, 1400, 30
(Target)
EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7369,SMITH,CLERK,7902,17-Dec-80,800,400,20
7499,ALLEN,SALESMAN,7698,20-Feb-81,1600,300,30
7521,WARD,SALESMAN,7698,22-Feb-81,1250,500,30
7566,JONES,MANAGER,7839,02-Apr-81,2975,400,20
7654,MARTIN,SALESMAN,7698,28-Sep-81,1250,1400,30
http://informaticachamp.blogspot.in/2014/04/scenario-13-b-how-to-convert-row-to.html
13: How to assign an id for specified group of records and achieve target below.
Source
Column1
A
B
C
*
D
E
F
G
*
H
I
J
Target
Column1
D
E
F
G
http://informaticachamp.blogspot.in/2014/04/scenario-17-how-to-assign-id-for.html
14: How to insert same group of data in single column.
Source
ID,NAME,DEPTNO
100,JOHN,10
200,WALTER,20
300,GIBSON,30
400,KERRY,20
500,PHILIP,10
Target
DEPT10,DEPT20,DEPT30
JOHN,WALTER,GIBSON
PHILIP,KERRY,
http://informaticachamp.blogspot.in/2014/05/scenario-20-how-to-insert-same-group-of.html