[resolved] Simple Count

One Star

[resolved] Simple Count

Hello
I just really wanted to make sure I understand the Count functionality within the tAggregateRow component.
I have a simple job:
tMSSqlinput ---> tAggregateRow ---> tLogRow
So, I have a SQL input that returns an ID (String DataType), fed into the aggregate to perform a count and output into the tLogRow.
I get the following error and empty table. Can anyone tell me why this is please?
Warning:the operation 'count' for the output column 'DriverID' can't be processed because of incompatible input and/or output types
.--------.
|tLogRow_1|
|=------=|
|DriverID|
|=------=|
|null |
'--------'

Thank you in advance.

Accepted Solutions
Community Manager

Re: [resolved] Simple Count

I see, but you just need to execute a query to count the number of rows and judge it to decide the next step. If you want to see how the count functionality of tAggregateRow works, you have to set a group element, if there is no group element avaiable in your case, simply add a new column and set it with a fixed value on tMap before tAggregateRow, and set the new column as group element on tAggregateRow. For example:
tMssqlInput--main--tMap--tAggregateRow--main--tLogRow
on tMap, add a new column in the output table, and set a fixed value to it, eg:1
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business

All Replies
Community Manager

Re: [resolved] Simple Count

Hi 
String type is incompatible for count function, try to convert it to Integer/int type before tAggregateRow component.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Simple Count

Hi shong
This did not work, the ID is alpha numeric and therefore not an integer.
I guess I am trying to gain an understanding on how to complete this without altering my source code by adding a column with the value 1 in each row. This to me as an easy way out and I feel Talend must be able to perform a simple count on a column containing records that have a string data type.
Thank you
Community Manager

Re: [resolved] Simple Count

Hi 
Can you please upload a screenshot of settings of tAggregateRow? It will be helpful for us to address the issue.
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Simple Count

Hi Shong.
Here it is.
 
Community Manager

Re: [resolved] Simple Count

Hi
You have to define one group column.
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Simple Count

Hi Shong
I see, but I don't want to group.
I just want an overall total. I.E. 
.--------.
|tLogRow_1|
|=-------|
|DriverID|
|=-------|
|5755 |
'--------'

I'm not sure i'm doing it the right way, I know this would be useful knowing the number of rows that are in a table before they are processed. Is there no easy way to achieve this?
Thanks
One Star

Re: [resolved] Simple Count

Hi Ricco,
What i understood here,you want know the total row count of table before processing data into flow.
Then use "((Integer)globalMap.get("tFileInputDelimited_2_NB_LINE"))" global variable.
Thanks
kumar
Community Manager

Re: [resolved] Simple Count

Hi 
You can execute a query on txxxInput such as tMysqlInput to counts the number of rows, for example:
"select count(DriverID) from yourTableName"
And define one column with int type on the schema of tMysqlInput.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Simple Count

Hi Shong
We are actually moving away from MS and towards Talend which is one of the reasons why I am looking to complete this simple exercise using Talend. The other reason I am trying to complete this solely using talend is to look at some simple jobs that I feel would be straightforward in SSMS/SSIS and how I could transfer these skills in Talend just to gauge my training requirements.
Thanks
Community Manager

Re: [resolved] Simple Count

I see, but you just need to execute a query to count the number of rows and judge it to decide the next step. If you want to see how the count functionality of tAggregateRow works, you have to set a group element, if there is no group element avaiable in your case, simply add a new column and set it with a fixed value on tMap before tAggregateRow, and set the new column as group element on tAggregateRow. For example:
tMssqlInput--main--tMap--tAggregateRow--main--tLogRow
on tMap, add a new column in the output table, and set a fixed value to it, eg:1
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Simple Count

Hi Shong.
I think i was misunderstanding how to correctly use the tAggregateRow. It makes sense now, thank you for your help.
Rich