Tag Archives: Powercenter

How to generate a star pattern using Informatica with a Java Transformation

This post is in continuation to my earlier post where I described how to generate a star pattern without using a Java transformation. In this post I will describe the method to generate this pattern using Java transformation.

Just for the benefit of readers who have not read my previous post, below is the description of the problem which we will solve using a Java transformation this time.

Question:- To generate a star pattern using Informatica depending on the number of rows in the input file

Suppose there are 7 rows in the source file (any type of data),  the target should have 7 rows of stars in the following pattern:-

FinalStarPattern

Solution:- 

Solving this problem using java transformation is easier than the solution i presented without java transformation in my previous post here.

In this mapping also we will have one pipeline to get the row count of the file using aggregator and set it into a mapping variable.

Second pipeline will use a java transformation and some java code magic to generate the required pattern.

Below is the complete mapping screenshot

map_with_java

Mapping explanation:-

Pipeline 1:- To get the row count in the file. We will use an aggregator to get the count of the rows in the file and an expression to set this count into a mapping variable. Below are the screenshots of the aggregator and the expression used

agg_count

exp_set_var

Pipeline 2:- Here we use the row count, generate a row sequence in and expression and then pass these two values to the java transformation.

exp_sequence

Use Java transformation in Passive mode and create following input and output ports

java_count1

Use below code to generate the star pattern and connect the finalPattern port to target.

java_count2

The code snippet used above is pasted below:-

starpattern = "";
spacepattern = "";

for (int i =0; i < sequence ; i++){

starpattern = starpattern + “*”;

}

for (int j =0; j< rowcount – sequence; j++){

spacepattern = spacepattern + ” “;

}

for (int i =1; i < sequence ; i++){

starpattern = starpattern + “*”;

}

finalPattern = spacepattern + starpattern;

Well that is all about solving this problem using a java transformation.

let me know in comments if you need any clarifications.

How to load only the first and last row from a flat file to a target file using Rank transformation?

This is a common interview question which can really test the candidates’ knowledge of informatica transformations and logical thinking.

The caveat here is you have to do this without using any pre-session commands

So here we need to single out the first and last row from the source file. Lets take an example source file with 5 rows delimited by commas.

a,”Go To Market”
c,”Find a soap”
e,”Take it to cashier”
b,”Make Payment”
d,”Come home”

Now our task is to populate a target file with the first and last row, so that it looks like as below

a,”Go To Market”
d,”Come home”

Given this task we can break into three steps
1. Find the first row from the file
2. Find the last row from the file
3. Combine the two and load it to target table

Lets take each of the above individually

STEP1. Find the first row from the file
This can be easily done by assigning a unique number, starting from 1, to each incoming row.
Then filtering out the rest except for the row with number assigned as 1. This will be the first row

HOW TO GENERATE UNIQUE NUMBER FOR EACH ROW?
Two methods – either use sequence generator or use expression variables to generate sequence numbers. I would prefer expression transformation. Here’s how you do it in an expression

1. Use expression transformation and create an integer variable port named v_sequence and put the following expression in the expression editor:

IIF(v_sequence=0,1,v_sequence+1)

This clause takes advantage of the informatica property to assign default values of 0 to integer ports.

2. Create an output port o_sequence and assign v_sequence to it in the expression editor.

3. From this expression feed the filter_first, which will let only the row with o_sequence =1 pass through (i.e. first row) and the Rank transformation which will give us the last row.

In addition to above also create a constant output port o_constant and set its value to 1.

Connect the fields from the file and the o_sequence and o_constant fields to the rank transformation for the last row.

STEP2. Find the last row from the file
This can be easily done by using the Informatica Rank transformation. Use the unique number as assigned to the rows in step above as the port to rank on. The highest number will get the top most rank that is 1. So we can then have a filter which will let pass only the row with RANKINDEX =1.

HOW TO FIND THE LAST ROW USING RANK
1. Use the o_constant field to group by on(check Group by Property) and use the o_sequence field to rank on(check R property). Also in properties select TOP as ranking order.

2. From this rank transformation connect all file fields and RANKINDEX field to a filter_last transformation where put the filter condition as RANKINDEX=1 which will let only the last row pass through.

STEP3. Combine the two rows and load the target file
Use Union operator to combine the two flows for first and last row and load the target.

This is self explanatory. In union transformation create two input groups connect each to first and last flow respectively. Connect the output to the target.

This question tests the knowledge of following transformations
1. Expression
2. Rank
3. Filter
4. Union

This can also be done by using Aggregator transformations but I believe it is not an elegant solution as the one described above.

And that is it! You have the first and last row.

Another way using aggregator