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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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!

[alevy]

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.

[Anonymous]

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

[Anonymous · Accepted solution]

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

[Anonymous]

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.

[Anonymous]

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

[TamilM]

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.

[Anonymous]

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