Hi everyone, Please help with the attached problem.

[SP3000]

Hi everyone,

Please help with the attached problem. I have made 2 sheets in the attached file, one for the input and second sheet is the desired output sheet. 

Thank you in advance!!

[mceleavey]

Hi @SP3000 ,

This is a bit of regex to replace the carriage return and then splitting to rows on the Supplier ID:

Workflow attached.

M.

[SP3000]

Hi M,

Thanks a ton for the quick help. 

The supplier id is sorted but the amt is not showing as per the desired output, can you please help? Thank you so much again in advance!

[apathetichell]

@mceleaveymay have posted the wrong file - your solution also uses regex but tokenizes and splits to rows.

[SP3000]

The amt column in still not showing as per the desired ouput. Can you please help?

[TheOC]

hey @SP3000 
What is the math/logic behind the distribution of the amt split? In the dataset you have given it is not obvious why for instance the first row allocates 1000 to "123" and 500 to "789"

Input:

Output:

Without knowing this, its not really possible to create something that will split the data in the way you asked. The amt would have to be manually set at this stage.

If you're using another data source for this information, i would recommend bringing that into the workflow instead.

An example of how that data could be brought in, using a text input tool, has been attached, in the case that this data is just a short-hand version of the actual data you have.

Cheers,
TheOC

[SP3000]

Hi OC,

Thank you so much for taking out your time to help.

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

Please find the attached updated query file.

Regards,

Sonam

[TheOC]

Hey @SP3000 

I see the issue! When you bring an excel spreadsheet into Alteryx, it doesn't keep the formula that excel uses to populate that column:

So within AMT, I'm hoping one of the other solution providers/experts knows how to bring that formula through too, and then it is more than possible!
If not, i would suggest editing your spreadsheet that you're bringing in, to present these values.

Hope this helps!
Owen

[apathetichell]

Yeah @TheOC is totally correct - I don't see how we can go from aggregated values in the third column to individual. If the numbers were there - you can use a tile strategy to get numbers of rows for each group and then a get word strategy where the final entry gets all of the remaining words but without that level of detail I don't really see a way.

[SP3000]

Hi Owen,

Thank you so much for the clarification. Actually the file I receive is a heavy and manually updated so I am looking for a solution  to run my logics as shared. 

I'll keep digging more for the solution and in the meantime if you can tag any expert who might can solve it then it'd be great.

Thanks again so much,

Best,

Sonam P