How to count rows with conditions

[Anonymous]

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.

[McJingles]

Try the TAggregateRow component to group by and count.

[Anonymous]

I will try.

Can i do a countif with this component ?

[Anonymous]

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

[Anonymous]

@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.

[Anonymous]

@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...

[cmendels · Accepted solution]

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.

[schwarben]

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