Analytical funtions are quite useful
in SQL and help us avoid extra coding.We encountered a similar scenario where
in we had to implement a function similer to the ROWNUM() OVER (partition by
clause) analytical functionality in Talend.
We ll give an overview of the Talend Job we created in order to attain the functionality:
Prerequisites:
Create a context variable and Initialize it to 0.
Scenario: Implement the ROW_NUMBER() over (partition by) analytical functionality in Talend
The image below gives the overview of the job, the components to focus on would be the tMemorizeRows and the tJavaRow component.
We ll give an overview of the Talend Job we created in order to attain the functionality:
Prerequisites:
Create a context variable and Initialize it to 0.
Scenario: Implement the ROW_NUMBER() over (partition by) analytical functionality in Talend
The image below gives the overview of the job, the components to focus on would be the tMemorizeRows and the tJavaRow component.
The input file is as shown in the
snapshot below:
We need output to look something like this:
The tMemorizeRows is configured as
shown in the snapshot below:
This configuration specifies that we
MEMORIZE the ID column , and the row count specifies that we remember upto 2
rows flowing into the tMemorize component.
The most important part of the job ,is configuring the
tJavaRow component ,wherein we specify the logic for implementing this
analytical function.
The java code compares the previous and
current row using the index for tMemorizeRows as shown in the snapshot..
