topic Re: [resolved] Simple Count in Talend Studio

[resolved] Simple Count

Anonymous — Tue, 07 Apr 2015 15:56:41 GMT

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.

Re: [resolved] Simple Count

Anonymous — Tue, 07 Apr 2015 16:06:32 GMT

Hi
String type is incompatible for count function, try to convert it to Integer/int type before tAggregateRow component.
Best regards
Shong

Re: [resolved] Simple Count

Anonymous — Tue, 07 Apr 2015 16:11:14 GMT

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

Re: [resolved] Simple Count

Anonymous — Tue, 07 Apr 2015 16:15:18 GMT

Hi
Can you please upload a screenshot of settings of tAggregateRow? It will be helpful for us to address the issue.

Re: [resolved] Simple Count

Anonymous — Tue, 07 Apr 2015 16:32:13 GMT

Hi Shong.
Here it is.

Re: [resolved] Simple Count

Anonymous — Wed, 08 Apr 2015 02:19:10 GMT

Hi
You have to define one group column.

Re: [resolved] Simple Count

Anonymous — Wed, 08 Apr 2015 08:13:34 GMT

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

Re: [resolved] Simple Count

Anonymous — Wed, 08 Apr 2015 12:28:09 GMT

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

Re: [resolved] Simple Count

Anonymous — Wed, 08 Apr 2015 21:39:14 GMT

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

Re: [resolved] Simple Count

Anonymous — Thu, 09 Apr 2015 12:08:11 GMT

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

Re: [resolved] Simple Count

Anonymous — Fri, 10 Apr 2015 06:51:20 GMT

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

Re: [resolved] Simple Count

Anonymous — Tue, 14 Apr 2015 10:34:07 GMT

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