topic Re: How to count rows with conditions in Talend Studio

How to count rows with conditions

Anonymous — Mon, 09 Dec 2019 12:41:07 GMT

Hello Guys,

How are you ?

I need to make a transformation and I don't know if talend is possible, but I imagine there is such a possibility.

Simply put I need to make a pivot table or a kind of "Query" in the table below.

It was built as a function of column "JOB" so there are repeated values for JOB and different values for other columns.

What I need to do is count how many times the "SITUACAO" column has "Encerrado", "Em Separação", "Aguardando Impressão" values for the same "JOB".

Ex of output i need :

JOB	Encerrado	Em Separação	Aguardando Impressão
1122865	17	2	0

Is it possible to do this on the talend?

If so, which component should I use to develop this code and if possible how would you do this code in java.

I would be grateful for any help.

Re: How to count rows with conditions

McJingles — Mon, 09 Dec 2019 12:51:48 GMT

Try the TAggregateRow component to group by and count.

Re: How to count rows with conditions

Anonymous — Mon, 09 Dec 2019 13:06:08 GMT

I will try.

Can i do a countif with this component ?

Re: How to count rows with conditions

Anonymous — Mon, 09 Dec 2019 14:21:29 GMT

I need to make a countif...

When i tried to use this component i face this error mesage

"Warning:the operation 'count' for the output column 'SITUACAO' can't be processed because of incompatible input and/or output types"

The column "SITUACAO" is string and i need to count the rowns number not the values

Re: How to count rows with conditions

Anonymous — Mon, 09 Dec 2019 16:46:31 GMT

@RenanAraujo, define one column with int type on the schema of tAggregateRow to receive the value of count function, select this column in the Output column list.

Re: How to count rows with conditions

Anonymous — Mon, 09 Dec 2019 18:24:29 GMT

@shong Ok....make sense.

But i need to count the row just if i match a condition in a string column...in this way i will jusy GroupBy and count all row...

Re: How to count rows with conditions

cmendels — Mon, 09 Dec 2019 21:37:31 GMT

Use a tJavaRow before your tAggregateRow to create new columns with the values you need. Example:

if (input_row.SITUACAO!=null && input_row.SITUACAO.length()>0 && input_row.SITUACAO.equals("Encerrado"))

{output_row.Encerrado = 1;}

else {output_row.Encerrado = null;}

Continue creating columns for each value you need and add them to your output schema. Then count those rows in tAggregateRow, grouping by JOB.

Re: How to count rows with conditions

schwarben — Thu, 18 Mar 2021 10:11:20 GMT

Alternatively to cmendels's solution one can also use a tMap to create a new column {1,0} column with the desired formula.