topic Re: Get a paticular value BEG[03] from a single line using formula in Workato Pros Discussion Board

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

MurugarajSa — Fri, 01 Dec 2023 06:33:47 GMT

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.

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

gary1 — Fri, 01 Dec 2023 06:50:17 GMT

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

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*"

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]

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

MurugarajSa — Fri, 01 Dec 2023 15:30:55 GMT

Thanks for your quick reply and it worked perfectly 😊

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

MurugarajSa — Mon, 04 Dec 2023 03:05:47 GMT

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.

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

gary1 — Mon, 04 Dec 2023 04:34:06 GMT

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

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

MurugarajSa — Mon, 04 Dec 2023 04:55:26 GMT

Hi @gary1

Thanks for your quick reply 😊.

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*RE*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*RE*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~

Above is the expected output result.

1st thing is Need to extract value of 823127 and 823128 from BEG segment and store it in a variable to use it later(concatenate would be fine. e.g 823127 | 823128 ). we may have mutiple BEG segment in a single line and length may vary between each BEG.

2nd thing is to update *RL* to *RE* in BEG segment. we can use gsub but do we have any other efficient way?

Thanks

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

gary1 — Mon, 04 Dec 2023 06:25:43 GMT

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:

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

Set up the output like this:

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!

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

MurugarajSa — Mon, 04 Dec 2023 10:40:34 GMT

Thank you very much for your solution @gary1 . it worked perfectly for my scenario.