Adding a Number To a Sequence

[alphabex18]

Hi everybody, 

I am working on transferring EDI data into a structured data set. I am stuck on adding two additional characters to some sequential text. In the pic below, i want to add "01", "02", "03"....to each of the segments shown, until they reach the last one, then start over for the next one. I have the expected results below the picture, and also attached the excel file. 

Here are my expected results for the Segment Column

ISA01

ISA02

ISA03

ISA04

ISA05

ISA06

ISA07

ISA08

ISA09

ISA10

ISA11

ISA12

ISA13

ISA14

ISA15

ISA16

ISA17

GS01

GS02

GS03

GS04

GS05

GS06

GS07

GS08

ST01

ST02

ST03

ETC......

[BenMoss]

First of all, lets get this incrimental Id as a seperate field.

To do this we can use the multirow formula tool.

Lets call this RowID and use the formula...

[row-1:RowID]+1

Then, make sure you check the field 'Segment' in the grouping option. This means that it will restart for every group.

Now we need to concat it with our segment field which we can do using a standard formula tool.

[Segment]+tostring([RowID])

If you wish to have the leading zeros then you can use the function PadLeft.

so...

[Segment]+Padleft(tostring(RowID),2,'0')

Have a try at implementing this and I can advise if you get stuck.

Ben

[Kenda · Accepted solution]

Hi @alphabex18

I would recommend first using  Multi-Row Formula tool. Create a new Int32 field and use the expression:

iif(isempty([Row-1:Segment]),1,iif([Row-1:Segment]=[Segment],[Row-1:New Field]+1,1))

Then, add a normal Formula tool and edit your Segment field with this expression:

[Segment]+iif(length(tostring([New Field]))=1,"0"+tostring([New Field]),tostring([New Field]))

That should do it!

[Claje]

Hi,

I've attached an example workflow which offers two methods to accomplish this.

The top method breaks the process down into multiple tools.  This method takes a little more work and probably runs a tiny bit slower, but I think it is easier to understand and maintain.

The bottom method uses only one tool (the multi-row formula tool) to create this value.  This is potentially more efficient, but there is some complexity to the formula and it is probably harder to easily understand.  I tried to add comments to document each step of the formula as some pieces are a little unintuitive.

[JohnJPS]

Use MultiRow formula to provide the counter, then a normal formula to combine it with the [Segment].

I've done this in the attached workflow, which also gives an "always-10-character" "SegmentID" by looking at the length of "segment" and adding enough zeroes such that things line up to 10 characters every time.

Hope that helps!

John

[ponraj]

Here is the sample workflow for your case. Hope this is helpful. 

WorkflowResults

[alphabex18]

This was great. I found that if i grouped the segment, it added more numerical values than i wanted. Thank you so much for your reply!