Four Stars

cyclic sequence number generation

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Six Stars

Re: cyclic sequence number generation

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")?nullSmiley SadRelational.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,

 

 

Veeranjaneyulu Boppudi
Eleven Stars

Re: cyclic sequence number generation

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

Rilhia Solutions
13 REPLIES
Eleven Stars

Re: cyclic sequence number generation

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)
Rilhia Solutions
Four Stars

Re: cyclic sequence number generation

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

Ten Stars

Re: cyclic sequence number generation

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

-----------
Eleven Stars

Re: cyclic sequence number generation

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.

Rilhia Solutions
Six Stars

Re: cyclic sequence number generation

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

Veeranjaneyulu Boppudi
Four Stars

Re: cyclic sequence number generation

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

 

Four Stars

Re: cyclic sequence number generation

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

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

 

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

Eleven Stars

Re: cyclic sequence number generation

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.

 

Rilhia Solutions
Six Stars

Re: cyclic sequence number generation

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")?nullSmiley SadRelational.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,

 

 

Veeranjaneyulu Boppudi
Four Stars

Re: cyclic sequence number generation

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

 

Eleven Stars

Re: cyclic sequence number generation

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

Rilhia Solutions
Four Stars

Re: cyclic sequence number generation

Hi rhall,

 

Thank you for your answers , now i got proper output 

Four Stars

Re: cyclic sequence number generation

Hi vboppudi,

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