topic Re: [resolved] Getting a row count before counting the rows. in Talend Studio

[resolved] Getting a row count before counting the rows.

Anonymous — Sat, 16 Nov 2024 12:49:39 GMT

Hi,
I'm trying to get an initial row count into a global variable based on a column, so for example:
I have 6 rows, the first 5 belong to one account holder (there is a ledger_id which is duplicated - it's a foreign key), the last one belongs to another account holder. In one column there is a requirement to put in direct debit descriptions which follows the format 'Payment No. of ' so, for example, the second row for the first account holder contains in the direct debit descriptions 'Payment No. 2 of 5'.
My question is, how do I get the '5'? I have tried to do it Talend. I can do this in SQL, but I need automation.
warm regards,
Lee

Re: [resolved] Getting a row count before counting the rows.

Anonymous — Wed, 06 Jul 2011 09:24:14 GMT

Hi
There is a global variable nb_line of txxxInput component which counts the total number of selected rows. For example:
((Integer)globalMap.get("tMysqlInput_1_NB_LINE"))

Best regards
Shong

Re: [resolved] Getting a row count before counting the rows.

Anonymous — Thu, 07 Jul 2011 00:06:19 GMT

Hi Shong, thanks for that info. What about when you're counting rows on a foreign key? So for example, when I reach 'Payment No 5 of 5', I need it to return to Payment No. 1 of 1 when the foreign key changes and it all starts again, so my table will end up looking something like this:
PK | FK | PaymentDesc |
200001 | 462763 | Payment No. 1 of 5 |
200002 | 462763 | Payment No. 2 of 5 |
200003 | 462763 | Payment No. 3 of 5 |
200004 | 462763 | Payment No. 4 of 5 |
200005 | 462763 | Payment No. 5 of 5 |
200006 | 348111 | Payment No. 1 of 3 |
200007 | 348111 | Payment No. 2 of 3 |
200008 | 348111 | Payment No. 3 of 3 |
It's the 5s and 3s I'm trying to get. I need to know these numbers in advance. I have thought about using the tAggregateRow component, but I'm unsure how to incorporate the globalMap object.
Also, how do know what key to use to access a value in the globalMap? Is there a template that Talend uses? Or a way of displaying them all Enumeratively?
Thanks in advance,
Lee
ps - Apologies for the barrage of questions!

Re: [resolved] Getting a row count before counting the rows.

alevy — Thu, 07 Jul 2011 01:38:28 GMT

You should read the table to count the number of records by FK (using tAggregateRow) and then join the results to your main data flow using tMap where you can construct the PaymentDesc. There is no need for globalMap.

Re: [resolved] Getting a row count before counting the rows.

Anonymous — Thu, 07 Jul 2011 06:25:37 GMT

Hi alevy, thanks for your suggestion. I certainly wish I could see what you were visualising but in my attempts to guess, I haven't been successful. What I'm unclear of is how to get the count for each foreign key and then carry this over into a Map as you suggested. The problem as I see it is due to the tAggregateRow not carring over the rest of the fields (inside its operations table - component View) without needing to perform a mandatory operation of some kind. Do I have to iterate over the same tAggregateRow or something similar? I cannot see how to do this.
Thanks,
Lee

Re: [resolved] Getting a row count before counting the rows.

Anonymous — Thu, 07 Jul 2011 08:26:00 GMT

Hi
Read all rows from the souce data, link it to a tAggregateRow, gourp by the FK, and get count number of each group, and then output the result to a tJavaRow there store the count number to global variable, FK is the key. For exmaple:
tMysqlInput_1(read FK column)--main--tAggregateRow--main--tJavaRow
|
onsubjobok
|
tMysqlInput_2(read PK, FK columns)--row3-->tMap-->tLogROW
on tAggregateRow: group by FK, and get the count number of each group
on tJavaRow:
globalMap.put("FK", input_row.count);
In the expression of output table of tMap:
"Payment No."+Numeric.sequence(row3.FK, 1,1)+" of "+(Integer)globalMap.get("FK")
Let me know if you still have any troubles.
Best regards
Shong

Re: [resolved] Getting a row count before counting the rows.

Anonymous — Fri, 08 Jul 2011 00:07:28 GMT

To whom do I send the Champagne and flowers?
It worked.
Thank you shong.
Sincere respects,
Lee
ps - It was tJavaRow that was missing from my original attempt. Thanks again, shong.

Re: [resolved] Getting a row count before counting the rows.

Anonymous — Fri, 08 Jul 2011 04:25:42 GMT

Hi
Glad to hear that!

To whom do I send the Champagne and flowers?

Good question, maybe we could add a new feature to forum!~
Best regards
Shong

Re: [resolved] Getting a row count before counting the rows.

TamilM — Thu, 12 Dec 2013 11:45:29 GMT

HI,
i have task , i need to get count of an input table and compare this count with output table count ,if there is an difference in count that diff need to be displayed in the output file (any file txt,doc,excel) .if no diff found display no diff found, im very new to Talend, so please guide me on this. thanks in advance.

Re: [resolved] Getting a row count before counting the rows.

Anonymous — Fri, 13 Dec 2013 07:24:06 GMT

Hi tamil19mynthan,
Could you open a new forum for your topic?
Best regards
Sabrina

Re: [resolved] Getting a row count before counting the rows.

Anonymous — Fri, 16 Oct 2015 11:08:22 GMT

hi ,
i am create a file to delete last three row in talend. I can use following statement for count the number of rows in table:
((Integer)globalMap.get("tMysqlInput_1_NB_LINE"))
but i get null value in table.

Re: [resolved] Getting a row count before counting the rows.

Anonymous — Thu, 31 Dec 2015 09:47:34 GMT

hi
how to get no of rows in the tmssqloutput?

Re: [resolved] Getting a row count before counting the rows.

Anonymous — Mon, 04 Jan 2016 02:47:59 GMT

Hi nsriharsha15,
Usually, Nb_LINE is used to count the total number of records have been proceed.
When inserted item is choosen, there will be a global variale such as ((Integer)globalMap.get("tMysqlOutput_1_NB_LINE_INSERTED"))will be avaible, which counts the total number of records have been inserted into database.
The same to updated, deleted items.
Best regards
Sabrina

Re: [resolved] Getting a row count before counting the rows.

sasi8008 — Mon, 04 Apr 2016 14:52:34 GMT

Hi, i have 200 jobs ,i need find row count,retrieved records,updated records and job componentst to each job
I need to joblet a to get the info about all jobs
Is there any way to create a joblet to jobs.
Using NB_LINES

Re: [resolved] Getting a row count before counting the rows.

Anonymous — Tue, 14 Jun 2016 11:47:38 GMT

hi,
how do i print ((Integer)globalMap.get("tJDBCInput_1_NB_LINE"));
regards,
sagar jadhav.

Re: [resolved] Getting a row count before counting the rows.

Anonymous — Wed, 15 Jun 2016 09:42:37 GMT

Hi,
You can use tfixedflow component to print ((Integer)globalMap.get("tJDBCInput_1_NB_LINE")) into console.
Best regards
Sabrina

Re: [resolved] Getting a row count before counting the rows.

_AnonymousUser — Wed, 15 Mar 2017 20:09:58 GMT

How do I get a column count that is visible in the talend run console and move that to an email with some formatting? I am using GPLoad, and I see the row counts for insert/delete/update/error within the log file. My problem is that even with the errors, the job completes successfully (though no records are inserted), and there is no notification for me to know this was a failed job. When I go back and take a look at the logs, I see the Error/insert/update counts, but it is a manual process for me. What I would like is to get the console message into email as an alert to particular list of users in case of job has not successfully added rows in the run.
So far, I have tried a tFileRowCount for the input csv file for gpload, and a select count(*) from target table, exporting that to a csv and running a binary tally between the two of them using tFileCompare, tAssert to throw up the match/mismatch, then tSendEmail.
tfileinput(raw file used in gpload) -- tgpload--tgpinput (select count(*)) -- tfileoutputdelimited (2)
|
tFileRowCount--(raw file+ (2))
|
tfilecompare --tassert - tsendmail
I feel like I am adding extra steps. Please help me.. Thanks

Re: [resolved] Getting a row count before counting the rows.

Anonymous — Thu, 16 Mar 2017 08:16:42 GMT

Hi N00biETL,

The tFlowMeterCatcher component catches the processing volumetric from the tFlowMeter component and passes them on to the output component.

You can use it in your work flow and send the number to you by using tsendmail component.

Best regards

Sabrina

Re: [resolved] Getting a row count before counting the rows.

Anonymous — Tue, 04 Apr 2017 08:39:37 GMT

Using tWriteXML component I am able to get this Output
----------------------------------
<Contact>
<row no="1">
<FL Name="Daouda Doumbia"></FL>
<FL Email="test1@test1.com""></FL>
<FL First_Name="Daouda"></FL>
<FL Last_Name="Doumbia"></FL>
<FL Phone="00001"></FL>
<FL Area="509"></FL>
</row>
<row no="2">
<FL Name="Zamantungwa Sikosana"></FL>
<FL Email="test@test1.com"></FL>
<FL First_Name="Zamantungwa"></FL>
<FL Last_Name="Sikosana"></FL>
<FL Phone="0"></FL>
<FL Area="(NULL)"></FL>
</row>

</Contact>
------------------------------------------
But desired output is something like this
------------------------------------------
<Contacts>
<row no="1">
<FL val="First Name">Scott</FL>
<FL val="Last Name">James</FL>
<FL val="Email">test@test.com</FL>
<FL val="Department">CG</FL>
<FL val="Phone">989898988</FL>
<FL val="Fax">99999999</FL>
<FL val="Mobile">99989989</FL>
<FL val="Assistant">John</FL>
</row>
</Contacts>
------------------------------------
Is it possible to get this kind of output using any XML component or i have to write java code?
I am attaching my job design.

Regards
Gulshan

Re: [resolved] Getting a row count before counting the rows.

Anonymous — Wed, 05 Apr 2017 10:22:36 GMT

Hi Gulshan,
We can not see your job design on forum. Could you please attach it again?
Best regards
Sabrina