Use the unique number as assigned to the rows in step above as the port to rank on.
This can be easily done by using the Informatica Rank transformation. In addition to above also create a constant output port o_constant and set its value to 1.Ĭonnect the fields from the file and the o_sequence and o_constant fields to the rank transformation for the last row. first row) and the Rank transformation which will give us the last row. From this expression feed the filter_first, which will let only the row with o_sequence =1 pass through (i.e. Create an output port o_sequence and assign v_sequence to it in the expression editor.ģ. This clause takes advantage of the informatica property to assign default values of 0 to integer ports.Ģ. Use expression transformation and create an integer variable port named v_sequence and put the following expression in the expression editor: I would prefer expression transformation. Two methods – either use sequence generator or use expression variables to generate sequence numbers.
HOW TO GENERATE UNIQUE NUMBER FOR EACH ROW? Then filtering out the rest except for the row with number assigned as 1. This can be easily done by assigning a unique number, starting from 1, to each incoming row. Combine the two and load it to target table Given this task we can break into three stepsģ. Now our task is to populate a target file with the first and last row, so that it looks like as below Lets take an example source file with 5 rows delimited by commas. So here we need to single out the first and last row from the source file. The caveat here is you have to do this without using any pre-session commands This is a common interview question which can really test the candidates’ knowledge of informatica transformations and logical thinking.