Adding a Number To a Sequence

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.

2018-08-23 08_04_05-Alteryx Designer x64 - Transform EDI 834 HIPPA version_081718.yxmd_.png

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

EDI_Daily 834_Output from Alteryx.xlsx

Output

Parse

Expression

Accepted answers

Kenda

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!

All comments

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

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.

Claje_SegmentNumber.yxmd

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

SegID.yxmd

ponraj

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

Workflow Results Results

Adding a Number To a Sequence.yxmd

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!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703