Complex regex problem

[SP3000]

Please help with the attached problem. 

The logics are:

1) if there are 2 supplier ids and 2 distribution in the amt field then the first supplier id will get the first amt distribution and likewise for the second supplier id. And same for 3 supplier ids and 3 amt distributions then it will be updated as per the amt distributions. 

For eg. Supplier ids      Input amt               Output amt

              123                 =1000+500                 1000

               456                                                     500

2) If there are 2 supplier ids but 3 amt distribution then 1st supplier id should be updated with the first amt distribution and for the 2nd supplier id the amt will be the addition of the 2nd and 3rd amt distribution.

For eg. Supplier ids      Input amt               Output amt

              123                 =1000+500+200         1000

              456                                                     700

2) If there are 3 supplier ids but 4 amt distribution then 1st supplier id should be updated with the first amt distribution and for the 2nd supplier id the amt will the 2nd amt distribution and the 3rd supplier will be the addition of the 3rd and 4th amt distribution.

For eg. Supplier ids      Input amt                              Output amt

              123                 =1000+500+200+400             1000

              456                                                                  500

              890                                                                  600

Thank you in advance

[apathetichell]

Didn't this get solved by @TheOC 's awesome reading as .xlsx as .xml solution here?

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Hi-everyone-Please-help-with-the-attac...

This isn't a regex problem - regex is about parsing text - your initial question had to do with joint text groups.

[TheOC]

hey @SP3000

as @apathetichell mentioned, i believe i solved this for you before?

Are you able to check that previous solution and let me know if it works for you? I attached the workflow on the comment.

Cheers @apathetichell 


TheOC

[SP3000]

Hi OC, 

The output is not showing correctly, can you please check? I downloaded from the link you have shared. 

Thanks,

SP

[atcodedog05 · Accepted solution]

Hi @SP3000 

Here @TheOC 's workflow modified. Seems like he forgot to include the group record id macro in the packaged workflow. I am recreating the same functionality using Multi-row formula tool.

Hope this helps 🙂

[apathetichell]

Here's a modified version which doesn't use the cleanse macro - again this just uses @TheOC 's workflow

[SP3000]

Thank you so much..

[atcodedog05]

Major credit goes to @TheOC who has done all the heavy lifting 🙂 i just did a minor fix 😅

[TheOC]

Oops! Definitely need to get into a better habit of exporting the workflow! 

Glad you got it sorted @SP3000 !

[SP3000]

Hi @TheOC ,

As always many thanks for your kind support.

I tried testing the workflow with another file with same problem but its not working. I uploaded the first file as ZIP file with .xlsx file format but it didn't work. I noticed the zip file is parsed as CSV which I'm not able to figure out. 

I have attached the excel, test workflow and screenshot file for your reference.

Thank you again..

Best,

SP3000