Alteryx workflow to split data based on certain critetria

[Abcdefg090921]

Hi everyone, am not very familiar with alteryx and will need some help to create a workflow from the attached data file. 

Based on the sample file, will need the ABCD information from "Compensation Description" in <Comp info> tab to be split into respective amounts based on the conditions table in <Criteria>. Best if it is possible to include the additional split under the same "Compensation Description" in <Comp info> and include 2 new descriptions as "AB" and "CD" under the same column. 

e.g. for Employee 01, ABCD = 26. 

Total Retro Salary + Salary = 16,000 

So need to add on the following rows (refer yellow highlighted rows):

AB = 17.50, CD = 8.50

Would really appreciate the help to create a workflow on this!

[KGT · Accepted solution]

I would do this by firstly creating a field to join on. 

• Not sure if your criteria table is always going to be this format or whether it can be created in a more "data-friendly" manner. Working with what we have, created a field that takes the "<" value.
• On the main dataset create a field that represents the maximum in a range. For every thousand, it would be CEIL([Compensation Amount (-/+)]/1000)*1000

Then do the calcs

• Join the lookup table fields on, do your calc, and then you have the extra data you need and can union this on to the original dataset.

Note: I've intentionally left this a little vague as it's about the method, not the result. I imagine that your actual issue differs slightly from just splitting ABCD and the criteria table could be designed 'nicer'. There are many ways to emulate the range join, but I think this is easiest and quickest processing in this case.