window function in talend

[Soumya_M]

Q. I have a table with 2 columns -A_number and B_number. For every A_number I need to get the count of unique B_number associated with it and also in the third column count of  unique numbers should be displayed. I achieved this in SQL using window function count, for the count column. But I'm finding it difficult to implement this in Talend.

SQL resultant table looks like this

Any suggestions how to implement it in Talend?

[Anonymous · Accepted solution]

You can use those SQL functions in your query bringing in the data if you wish.

A slightly more complicated way of doing it is to use a tSortRow component to sort by columns A and then B, then look for changes between the rows in a tMap. Whenever a row is matched with the previous row for column A, column B will be counted if it is different to the previous column B value. By sorting in this way, you will only need to read the complete data set once