topic Re: cyclic sequence number generation in Talend Studio

cyclic sequence number generation

Mkrishna — Tue, 18 Apr 2017 08:34:11 GMT

i have to load a file with header detail and footer record in to a single file but when we load the records separate sequence number should be created for header and detail record, how can we achieve this.

example source

BIA00000000051070UPAULINA
SVA00000000051070S000009003RESFee
SVA00000000051070S000009003RESSupply
MTA00000000051070S000009003M000004576EL
BIA00000000091687UJACKSON
SVA00000000091687S000001365RESFee
SVA00000000091687S000001365RESSupply
MTA00000000091687S000001365M000001772E

Here BI is header and sva is detail ,mat is footer records

Req: i need to generate two sequence no as below as

seaqno

1 BIA00000000051070UPAULINA
1 1 SVA00000000051070S000009003RESFee
1 2 SVA00000000051070S000009003RESSupply
1 3 MTA00000000051070S000009003M000004576EL
2 BIA00000000091687UJACKSON
2 1 SVA00000000091687S000001365RESFee
2 2 SVA00000000091687S000001365RESSupply
2 3 MTA00000000091687S000001365M000001772E

Re: cyclic sequence number generation

Anonymous — Tue, 18 Apr 2017 09:39:45 GMT

You can do this by providing something unique for each header as the name of the sequence. Using the code below, the sequence will be reset every time the headerKey value changes.

Numeric.sequence(headerKey,1, 1)

Re: cyclic sequence number generation

Mkrishna — Tue, 18 Apr 2017 12:16:09 GMT

hi thank you for your valuable solutions,

can you just explain it please how can we achieve this , because iam also using the same funtions in tmap as :

Numeric.sequence(headerKey,1, 1)

here iam getting only the sequence number for all the rows as 1.2...3.4..5..6..7..............

Re: cyclic sequence number generation

vapukov — Tue, 18 Apr 2017 12:50:56 GMT

do You want have single sequence column:
1
11
12
2
21

or 2 columns
1,
1,1
1,2
2
2,1

generally You can use tJavaFlex and check for Footer
if row not a footer - use stored number for 1st digit and sequence for 2nd
if row is footer - change number for 1st and reset 2nd

Logic for both the same, just in case of single column need more math

Re: cyclic sequence number generation

Anonymous — Tue, 18 Apr 2017 14:00:13 GMT

OK, your data looks like this.....

BIA00000000051070UPAULINA
SVA00000000051070S000009003RESFee
SVA00000000051070S000009003RESSupply
MTA00000000051070S000009003M000004576EL
BIA00000000091687UJACKSON
SVA00000000091687S000001365RESFee
SVA00000000091687S000001365RESSupply
MTA00000000091687S000001365M000001772E

The BIA prefixed values are the header rows, the SVA values are the details and the MTA values are the footers. Now it looks like you wish to include your footer rows in this sequence count from your example. Is this the case? Also, I am assuming that you can control the order of this data. If this is the case, you can use this tutorial (https://www.rilhia.com/quicktips/quick-tip-compare-row-value-against-value-previous-row) to show you how you can keep track of your header row indicator (the BIA field value) between rows. You would use this value as the headerKey with the following code....

Numeric.sequence(headerKey,1, 1)

When it comes to the sequence per header row, you can also use the tutorial for this. Every time the header changes all you need to do is to increment a count by 1 yourself in a tMap variable.

Re: cyclic sequence number generation

vboppudi — Tue, 18 Apr 2017 15:41:56 GMT

Hi Mahadeva,

Please let me know if you are looking for the following output.

Starting job job_CyclicSequence at 09:28 18/04/2017.

[statistics] connecting to socket on port 3689
[statistics] connected
.-------+-------+---------------------------------------.
| tLogRow_1 |
|=------+-------+--------------------------------------=|
|HDR_SEQ|DTL_SEQ|ID |
|=------+-------+--------------------------------------=|
|1 |null |BIA00000000051070UPAULINA |
|1 |1 |SVA00000000051070S000009003RESFee |
|1 |2 |SVA00000000051070S000009003RESSupply |
|1 |3 |MTA00000000051070S000009003M000004576EL|
|2 |null |BIA00000000091687UJACKSON |
|2 |1 |SVA00000000091687S000001365RESFee |
|2 |2 |SVA00000000091687S000001365RESSupply |
|2 |3 |MTA00000000091687S000001365M000001772E |
'-------+-------+---------------------------------------'

[statistics] disconnected
Job job_CyclicSequence ended at 09:28 18/04/2017. [exit code=0]

I used tFileinputDelimited--> tMap-->tLogRow components. Used variable in tMap component to generate seq for Header and Footer. I am not able to provide screen shot here.

Regards,

Veeru

Re: cyclic sequence number generation

Mkrishna — Wed, 19 Apr 2017 08:19:51 GMT

Hi thank you for your answers...........

here i achieved sequence for footer records like

1 2 3 123 by using numeric.sequence(s1,1,1) function in tmap

but now i need to generate for header 111 222 333.

how did you achieved in tmap can you show me please

Re: cyclic sequence number generation

Mkrishna — Wed, 19 Apr 2017 09:29:36 GMT

thank you for your valuable answers.

Here i need two sequence column

Req: i need to generate two sequence no as below as

seaqno, seqno

here i achieved second sequence no by using numeric.sequence("s1,1,1) function in tmap.

can you please tell me for the first seqno

Re: cyclic sequence number generation

Anonymous — Wed, 19 Apr 2017 09:44:01 GMT

You can do this by extrapolating a little from the tutorial I sent you (https://www.rilhia.com/quicktips/quick-tip-compare-row-value-against-value-previous-row). In the "order_type" variable section we are identifying whether the order is an "Amendment Order" or an "Original Order". In terms of your example you can use this to identify a "new section" (requiring a new number) or an "existing section" (requiring reuse of the last number).

To achieve this simply create an integer tMap variable called "count" with the logic (assuming "order_type" is changed to "section")....

Var.section.compareToIgnoreCase("new section")==0 ? Var.count++ : Var.count

....then use your "count" variable to populate your first sequence. Ensure that you set "count" to be Not Nullable. This should do it for you.

Re: cyclic sequence number generation

vboppudi — Wed, 19 Apr 2017 12:42:09 GMT

The job flow is like below.

tFileInputDelimited--> tMap-->tLogRow

Data:

Src:

COL1
BIA00000000051070UPAULINA
SVA00000000051070S000009003RESFee
SVA00000000051070S000009003RESSupply
MTA00000000051070S000009003M000004576EL
BIA00000000091687UJACKSON
SVA00000000091687S000001365RESFee
SVA00000000091687S000001365RESSupply
MTA00000000091687S000001365M000001772E

Component

tMap:

row1:
COL1
BIA00000000051070UPAULINA
SVA00000000051070S000009003RESFee
SVA00000000051070S000009003RESSupply
MTA00000000051070S000009003M000004576EL
BIA00000000091687UJACKSON
SVA00000000091687S000001365RESFee
SVA00000000091687S000001365RESSupply
MTA00000000091687S000001365M000001772E

Variables:
HDR_SEQ--> Int--> StringHandling.LEFT(row1.COL1,3).equals("BIA")?(Relational.ISNULL(Var.PRE_HDR_SEQ)?1:Var.PRE_HDR_SEQ+1):Var.PRE_HDR_SEQ
PRE_HDR_SEQ-->int-->Var.HDR_SEQ

DTL_SEQ-->int-->StringHandling.LEFT(row1.COL1,3).equals("BIA")?nullRelational.ISNULL(Var.PRE_DTL_SEQ)?1:Var.PRE_DTL_SEQ+1)
PRE_DTL_SEQ-->int-->Var.DTL_SEQ

output:
HDR_SEQ-->Var.HDR_SEQ
DTL_SEQ-->Var.DTL_SEQ
ID--> row1.COL1

.-------+-------+---------------------------------------.
| Target_Data |
|=------+-------+--------------------------------------=|
|HDR_SEQ|DTL_SEQ|ID |
|=------+-------+--------------------------------------=|
|1 |null |BIA00000000051070UPAULINA |
|1 |1 |SVA00000000051070S000009003RESFee |
|1 |2 |SVA00000000051070S000009003RESSupply |
|1 |3 |MTA00000000051070S000009003M000004576EL|
|2 |null |BIA00000000091687UJACKSON |
|2 |1 |SVA00000000091687S000001365RESFee |
|2 |2 |SVA00000000091687S000001365RESSupply |
|2 |3 |MTA00000000091687S000001365M000001772E |
'-------+-------+---------------------------------------'

Regards,

Re: cyclic sequence number generation

Mkrishna — Thu, 20 Apr 2017 07:50:38 GMT

Hi rhall , thank you for your answers

here i did the same things in the following way but i am getting error , can you suggest my where i did a mistake

Var.Prev_row.compareToIgnoreCase(row1.Account_no)==0? Var.Count1:Var.Count+1 ..... ....integer

row1.Account_no = Var.Prev_row ......string

0=Var.Count ......integer

Var.Count= Var.Count1 ..... integer

Re: cyclic sequence number generation

Anonymous — Thu, 20 Apr 2017 10:55:24 GMT

What error did you get?
Can you post a screenshot of your configuration as well?

Re: cyclic sequence number generation

Mkrishna — Sat, 22 Apr 2017 17:01:39 GMT

Hi rhall,

Thank you for your answers , now i got proper output

Re: cyclic sequence number generation

Mkrishna — Mon, 24 Apr 2017 08:40:24 GMT

Hi vboppudi,

thank you for your answers , i got a proper output .

Re: cyclic sequence number generation

Anonymous — Wed, 06 Dec 2017 09:53:16 GMT

Hi All,

i have to generate the sequnce numbers for each record with tJava, tJavaFlex.

Thanks & Regards,

Jaineesh.

Re: cyclic sequence number generation

TRF — Wed, 06 Dec 2017 11:18:46 GMT

Numeric.sequence is here for this purpose