How to index rows based on column values

VNovitskiy — Fri, 15 Nov 2024 23:29:29 GMT

Hello!

I'm a fairly new user, and still exploring different components and trying to understand how they function. I have ran into a scenario that I know how to solve using SQL, but I was wondering what it would take to implement the logic in a job instead.

Let's say I have a flow coming from SQL input that looks like this:

City Item Quantity

Seattle House 1

NY House 3

Seattle Tower 5

Orlando House 2

Orlando Tower 3

Seattle Pool 4

I want to add a column to it, which will show index value based on the values in the City column, counting unique occurrences. It should look something like this:

City Item Quantity Index

Seattle House 1 1

NY House 3 1

Seattle Tower 5 2

Orlando House 2 1

Orlando Tower 3 2

Seattle Pool 4 3

The simplest way I can think of doing this in a job would be doing it all in Java using tJava or maybe tJavaFlex - creating a map collection, incrementing values based on a key, then converting it back to a flow. Or should I just do it all in SQL in the input block?

Thank you!

Re: How to index rows based on column values

Anonymous — Thu, 25 Nov 2021 07:12:22 GMT

In the output table of tMap, add a new column and set its expression as:

Numeric.sequence(row1.CityColumnName,1,1)

Regards

Shong

topic Re: How to index rows based on column values in Talend Studio

How to index rows based on column values

Re: How to index rows based on column values