Hi readers on daily basis i’ll be posting few scenarios discussed in IT toolbox here in my blog as most of my readers wrote to me regarding few interview based scenario’s i’m posting the same here. In case any one needs any help with these scenarios please feel free to contact me .
Skipped record count:
Scenario 1: My source files as 5, 00,000 records. While fetching its skipping records due to data type and other issues. Finally it fetches only 1, 00,000 records. Through session properties we are considering 100000 as the source count.
But actually we are loosing 400000 records. How can I find the number or records that are skipped?
Solution: OPB_SESS_TASK_LOG there is a count for SRC_FAILED_ROWS
Scenario 2: Please provide different ways to achieve this..
How to normalize the following data:
id date
101 2/4/2008
101 4/4/2008
102 6/4/2008
102 4/4/2008
103 4/4/2008
104 8/4/2008
O/P - should have only one id with the min(date)
How to create a mapping for this ?
1 ---> Using Transformations
2 ---> Using SQ Override method..
Solution: You can use the rank transformation and select rank port for id and group by on date. In properties tab select bottom and number of ranks as '1' .
Scenario 3: My scenario is like I am loading records on a daily basis, target is not a truncate load suppose from source I am loading records like
ID|Name
101|Apple
102|Orange
102|Banana
but in target I am already having target record(ID 102 of 10 records), scenario I need is like I have to delete only Empid 102 of yesterday record and load today record(2 records)
How to achieve this in Informatica?
Solution: You can achieve your goal by taking the Look up on your target table and match on the basis of ID column. Then take an expression after your lookup and add a FLAG column. In that FLAG column check for the NULL value return from Look up. After expression take 2 filters and in one filter pass the records with NULL values and Insert those records into Target.
If the Value is not NULL then you can take a UPDATE strategy and Update the old row with the new one.
Scenario 4:
Informatica Sequence Generator Scenarios
1) I have following set of records
Id | Name
101 | ABC
102 | DEF
101 | AMERICA
103 | AFRICA
102 | JAPAN
103 | CHINA
I need to generate sequence and populate in Target in the following manner
SID | ID | NAME
1 |101 | ABC
2 |101 | AMERICA
1 |102 | DEF
2 |102 | JAPAN
1 |103 | AFRICA
2 |103 | CHINA
How to implement the same in Informatica?
Solution:
1 sort on Id
2 use expression t/f like below
V_cur_id -> v_pre_id
V_pre_id -> i_id -- i_id is input
V_seq_id -> iif(v_cur_id = v_pre_id, v_seq_id+1, 1) --default 0
O_seq_id -> v_seq_id.
Scenario 5 : I have my input as below : ( Using Expression)
10
10
10
20
20
30
O/P :
1
2
3
1
2
1
How do we obtain this using the Informatica?
Solution: first import source, then use a sorter transformation . sort it by ur column, then use a expression.
in expression make this column .
like this
1.column_num(coming from sorter)
2.current_num= check if column_num=previous_num,then add (first_value +1),else 1
3.first_value=current_num.
4.previous_num(new column)= column_num
pass current_num to target.
No comments:
Post a Comment