How to eliminate 1st and last rows from the source and load
the inbetween rows.
- 5 Answers
- 24136 Views
- Syntel, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / kamlesh mishra
The PowerCenter Server skips the specified number of rows
before reading the file. Use this to skip header
rows in the file.
And for last rows implement following logic.
1) Take sorter transformation reverse the rows on the basis of some key field like empno.
2)take expr transformation add nextval from sequence generator
3) Take filter transformation add condition nextval !=1 .
4)(optional)reverse the rows again using sorter transformation.
5) From filter to tgt connect the respective ports.
Src---->SQ--->SORTER--->EXPR---->FILTER---->SORTER---->TGT
| Is This Answer Correct ? | 6 Yes | 0 No |
Answer / allen
Skipping the firt row in a flat file is easy:
The PowerCenter Server skips the specified number of rows
before reading the file. Use this to skip title or header
rows in the file.
Skipping the last row is not as easy. In my case, the last
row was a record count. I was able to check ports that
shouldn't be null, spaces or zeros and reject the row. The
LENGTH function can also be used to check the length of
data. Delimited or not has to be considered in any checks.
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / manoj subramanian
I have done it using sequence generator,rank and filter transformation.
Filtering first record:
Generated sequence no using sequence transformation and given
as input to rank transformation.In properties selected
top and no of ranks as maximum rank allowed in informatica
then followed by filter transformation with filter condition
as rankindex<>1.
Filtering last Record:
Followed by another set of rank and filter wit rank property as bottom.
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / sagarreddy
in source qualifier your using this query like this u get it
easily
select rowid from tablename
minus
(select min(rowid) from tablename
union
select max(rowid) from tablename )
if u write this query will work it plz go through it
| Is This Answer Correct ? | 1 Yes | 3 No |
Answer / maneesh
To eliminate first row, simply use a counter in Expression and then Filter.
To eliminate the last row, make two pipelines with the same source
In first pipeline, use AGGREGATOR without any Group By ports In second pipeline, join it with the above with JOINER. Use and Outer Join and then other than that Joined row .. load all the rows.
| Is This Answer Correct ? | 0 Yes | 4 No |
What are the different types of Type2 dimension maping?
WHAT IS UPDATE OVERRIDE . DIFFERENCE BETWEEN SQL OVERRIDE AND UPDATE OVERRIDE ?
How do you take care of security using a repository manager
1)can anyone explain how to use Normalizer transformation for the following scenario Source table | Target Table | Std_name ENG MAT ART | Subject Ramesh Himesh Mahesh Ramesh 68 82 78 | ENG 68 73 81 Himesh 73 87 89 | MAT 82 87 79 Mahesh 81 79 64 | ART 78 89 64 | please explain what should be the normalizer column(s) The GCID column 2)Also please explain the Ni-or-1 rule.
5 Answers FCS, IBM, Satyam, TCS,
When the informatica server marks that a batch is failed?
what is difference between dimention table and fact table?
How to eliminate duplicates in FF and oracle both
What is substr in informatica?
what is the flow?
how we load the data from source to staging area
What is an unconnected transformation?
How will restrict values in 0-9 and A-Z and a-z and special character.Only allowed these chars otherwise we will reject the records? what is the function we used to restrict...
Teradata 
