cancel
Showing results for 
Search instead for 
Did you mean: 

Get a paticular value BEG[03] from a single line using formula

MurugarajSa
Deputy Chef II
Deputy Chef II

Hi 

ISA*00* *00* *ZZ*TEST *ZZ*HOGAN *231129*1551*U*00401*000000001*0*P*>~GS*PO*MARY*HOG*20231129*1551*1*X*004010VICS~ST*850*0001~BEG*00*RL*823120**20231129~FOB*DF~ITD*05*2*****30~

I will receive one file and will have above line where length might be vary based on the file. I need to get value of  "823120" and store it in a variable to use it later.

BEG*00*RL*823120 -- I would say 3rd argument of BEG segment.

Any formula I can use to get the value from a single line

Thanks in advance.

 

2 ACCEPTED SOLUTIONS

gary1
Executive Chef II
Executive Chef II

 

This finds the location of "BEG", slices off the rest of the string, splits the remainder into an array, and returns the 4th index.

gary1_0-1701413127072.png

Because "BEG" might appear elsewhere in the string, this one may be more reliable if the start of the "BEG" segment is always formatted as "~BEG*"

gary1_1-1701413289185.png

Here's the formula as text so you can copy/paste. Just replace both "x" variables with the data pill.

x.slice(x.index("~BEG*")..-1).split('*')[3]

View solution in original post

gary1
Executive Chef II
Executive Chef II

I don't think all of this could be done in a single line (at least not by me!), so I wrote a quick code snippet you can use in a Ruby action. It will output the following:

  • begIDs: an array of BEG ID values
  • begSegmentsFinal: an array of updated BEG segments after replacing *RL* with *RE* -- this is mostly for reference, but I included it anyway
  • updatedInput: this is the entire string input updated after replacing *RL* with *RE*

Here's the actual output:

gary1_2-1701670966961.png

Set up a Ruby action with one string input called "string", and pass in your string.

gary1_0-1701670531650.png

Set up the output like this:

gary1_1-1701670611126.png

 

Use this code:

 

 

string = input[:string]

begSegments = string.scan(/(~BEG\*\d{2}\*[A-Z]{2}\*\d+\*\*)/).flatten
begIds = []
begSegmentsFinal = []
updatedInput = ''

begSegments.each { | s | 
  begIds.push(s.split('*')[3])
  begSegmentUpdated = s.include?('*RL*') ? s.gsub('*RL*','*RE*') : s
  begSegmentsFinal.push(begSegmentUpdated)
  updatedInput = string.gsub(s, begSegmentUpdated)
}

{
  begIds: begIds,
  # if you want a comma-delimited list, just use begIds.join(',') but you'll also have to update the output schema so begIds is a string and not an array
  begSegmentsFinal: begSegmentsFinal,
  updatedInput: updatedInput
}

 

 

It first uses a regex to find the BEG segments up to the ID value. Then it loops through the array of matches and uses gsub to replace RL/RE. In my opinion, this is a bit more selective/careful because it's only doing it on a portion of the BEG segment and not the entire input string where other instances of *RL* might exist, but I have no idea.

There are probably better or more efficient ways of doing this, but this should work until it doesn't!

View solution in original post

7 REPLIES 7

gary1
Executive Chef II
Executive Chef II

 

This finds the location of "BEG", slices off the rest of the string, splits the remainder into an array, and returns the 4th index.

gary1_0-1701413127072.png

Because "BEG" might appear elsewhere in the string, this one may be more reliable if the start of the "BEG" segment is always formatted as "~BEG*"

gary1_1-1701413289185.png

Here's the formula as text so you can copy/paste. Just replace both "x" variables with the data pill.

x.slice(x.index("~BEG*")..-1).split('*')[3]

MurugarajSa
Deputy Chef II
Deputy Chef II

Thanks for your quick reply and it worked perfectly 😊

MurugarajSa
Deputy Chef II
Deputy Chef II

Hi @gary1 

ISA*00* *00* *ZZ*TEST *ZZ*OTFWTESTUS *231201*2330*U*00401*000000003*0*P*>~GS*PO*TEST*OTFWTESTUS*20231201*2330*3*X*004010VICS~ST*850*0003~BEG*00*RL*823127**20231201~FOB*DF~ITD*05*2*****30~DTM*001*20231231~DTM*010*20231201~N1*ST*Georgetown*92*172002~N2*test, inc~N3*3059 M Street NW~N4*Washington*DC*20007~PO1*1*3*EA*20.90*WE*UP*816218025779*VC*5779~PO1*2*6*EA*20.90*WE*UP*816218026042*VC*6042~PO1*3*3*EA*19*WE*UP*816218028749*VC*8749~CTT*3~SE*15*0003~ST*850*0004~BEG*00*RL*823128**20231201~FOB*DF~ITD*05*2*****30~DTM*001*20231231~DTM*010*20231201~N1*ST*Georgetown*92*172002~N2*test, inc~N3*3059 M Street NW~N4*Washington*DC*20007~PO1*1*3*EA*20.90*WE*UP*816218025786*VC*5786~PO1*2*3*EA*20.90*WE*UP*816218026004*VC*6004~PO1*3*6*EA*20.90*WE*UP*816218026028*VC*6028~CTT*3~SE*15*0004~GE*2*3~IEA*1*000000003~

They have changed the file content that we will get multiple BEG segment in a single line, I want to get 4th index of the  BEG segment each(we may have multiple BEG in a single line) and update 3rd index that is RL to RE wherever BEG segment is present.

I am not able to do that as length is varying between each BEG segment.

Could you please help me on this? it would be very helpful for me.

Thanks so much in advance.

gary1
Executive Chef II
Executive Chef II

Can you provide an example of what you expect as output from the formula when processing the new input text?