0

So i a bit lost and don t really know how to hang up this one...

Consider that i have a 2 DB table in Talend, let say firstly A table invoices_only which has as fields, the invoiceNummer and the authors like this

enter image description here

Then, a table invoices_table with the field (invoiceNummer, article, quantity and price) and for one invoice, I can have many articles, for example

enter image description here

and through a tmap want to obtain a table invoice_table_result, with new columns, one for the article position, an one other for the total price. for the position i know that i can use something like the Numeric.sequence("s1",1,1) function, but don t know how to restart my counter when a new invoices nummer is found, and of course for the total price it is just a basic multiplication

so my result should be some thing like this

enter image description here

Here is a draft of my talend job, i m doing a lookup on the invoicenummer between the table invoice_only and invoices enter image description here

Any Advices? thanks.

Reims
  • 477
  • 1
  • 4
  • 12

2 Answers2

2

A trick I use is to do the sequence like this:

Numeric.sequence("s" + row.InvoiceNummer, 1, 1)

This way, the sequence gets incremented while you're still on the same InvoiceNummer, and a new one is started whenever a new InvoiceNummer is found.

Ibrahim Mezouar
  • 3,981
  • 1
  • 18
  • 22
  • 1
    As @Jim Macaulay said in his answer, dont forget to reset your sequence if the part of the job calling the sequence is itself called multiple times. – Carassus Jul 08 '20 at 13:08
0

There are two ways to achieve it,

tJavaFlex
Sql

tJavaFlex
You can compare current data with the previous data and reset the sequence value using below function,

if () {      
Numeric.resetSequence(seqName, startValue); 
}

Sql
Once data is loaded into the tables, create a post job and use an update query to update the records. You have to select the records and take the rank of the values. On top of the select you have to perform the update.

select invoicenumber, row_number() over(partition by invoicenumber, order by invoicenumber) from table name where -- conditions if any.

Update statements vary with respect to the database, please provide which database are you using, so that can provide the update query.

I would recommend you to achieve this through Sql

Jim Macaulay
  • 4,709
  • 4
  • 28
  • 53
  • Thank you Jim, it is a Microsoft SQL database, i found a way with the sequence number. – Reims Jun 24 '20 at 07:13
  • The problem with the solution with the database is that i don t want to open 2 connexion with the DB, and that wil be the case with a post job. – Reims Jun 24 '20 at 07:14