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

Four Stars

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

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

Accepted Solutions
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business

All Replies
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
Four Stars

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

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! Smiley Wink
Seven Stars

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

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.
Four Stars

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

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
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
Four Stars

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

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.
Community Manager

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

Hi
Glad to hear that!Smiley Wink
To whom do I send the Champagne and flowers?

Good question, maybe we could add a new feature to forum!~
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Six Stars

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

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

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

Hi tamil19mynthan,
Could you open a new forum for your topic?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.

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

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.
One Star

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

hi
how to get no of rows in the tmssqloutput?
Moderator

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

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

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

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
One Star

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

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

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

Hi,
You can use tfixedflow component to print ((Integer)globalMap.get("tJDBCInput_1_NB_LINE")) into console.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

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

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 Smiley Happy
Moderator

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

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

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

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

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
Moderator

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

Hi Gulshan,
We can not see your job design on forum. Could you please attach it again?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.