Alteryx workflow to split data based on certain critetria

Question

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 tab to be split into respective amounts based on the conditions table in . Best if it is possible to include the additional split under the same "Compensation Description" in 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! Test Data file.xlsx Screenshot 2024-11-04 102908.png

KGT · Accepted Answer

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.