cyclic sequence number generation

[Mkrishna]

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

[Anonymous]

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)

[Mkrishna]

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

[vapukov]

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

[Anonymous]

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.

[vboppudi]

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

[Mkrishna]

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

[Mkrishna]

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

[Anonymous]

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.

[vboppudi · Accepted solution]

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,