This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
This time i meet some challenge designing the alteryx logic.
First, I should make some random sample in my "population pool" tab, 100 sample out of 200 population.
This should be fine when i use the "Random % sample".
Second, I should allocate these sample result to reviewer to let them review the account status, and this time i meet some challenges.
To this allocation, I have 3 criteria:
The reviewer cannot review the account belongs to the type which reviewer is responsible for. To be more specific which means:
Tab1 shown some of the sampling results, amd the result should be allocated to the follwing reviewer name.
Tab2 shown the reviewer name, Type 1/2/3 means reviewer responsbile type account, (which means Alice is responsible for Type A account, Ben is responsible for Type B&D accounts, etc. ) As a result, Account150 cannot be allocated under Zoe's work, because Account 150 is G type, and Zoe herself is responsible for G type account. Account 2 allocated to Ben is correct.
To ensure the whole scale of sample. Since my sampling no is 100 accounts, if we find the above Account 150 condition, we cannot just left the account 150 gone, instead, we should allocate this account to another reviewer, to strictly ensure that the total 100 samples have been all correctlyl allocated.
The no. of the accounts allocated to reviewer should keep in similar level. Since we have 7 accounts in total as sample, and we have 3 reviewers, in average, each reviewer should received 2-3 accounts to review. Thus, if Alice has been allocated to 5 accounts to review, which is not allowed.
I have upload my Alteyrx workflow, you can directly design the logic based my uploaded Alteyrx logic in accordance with the above 3 criteari, if you have any brilliant idea.
I have attached a workflow that provides you with the answers you seek. I used an iterative macro that ran 112 loops in under 2 seconds to provide the following.
The workflow provides other reports by account and account type as well.
I took this on as a challenge since it was similar to an issue I ran across in an Accounts Receivable collections solution. I also thought this workflow would be a good fit for mock drafts, March Madness and other fantasy sports questions. It gave me a chance to hone my skills thinking thru how to use an iterative macro to solve these types of problems (very similar thought process to knapsack and #SANTALYTICS challenges). I was not able to provide a lot of documentation without taking up too much time during my weekend, but will be happy to answer any questions on my methodology and tool choice. Let me know if this provides the solution you are looking for.
1) The reviewer cannot review the account belongs to the type which reviewer is responsible for. 2) To ensure the whole scale of sample. 100% of accounts assigned 3) The no. of the accounts allocated to reviewer should keep in similar level
Chose random accounts and remove Account No. string. Sample tool set to allow 100% of records for testing.
Count the unique accounts and reviewers.
Calculate the maximum accounts a reviewer should handle to meet your 3rd
Count the restriction on account type, concatenate the restricted types into 1 field
Append each account and corresponding type to each reviewer. Make sure Append Fields is set to Allow All Appends
Exclude accounts from each reviewer that are in their restricted type list – takes care of 1st requirement
Merge the max accounts per reviewer into the data set
This is the population of accounts allowed to be reviewed by each reviewer
Add columns that will be used in Macro. Fields set to 1 or Null()
After the macro – Sort and Report as well as auditing the results to ensure goals were achieved
Filter out records that don’t match criteria 3
Assign priority to accounts – those with fewest count are given highest priority
Assign priority to reviewer – those with the highest number of accounts left and fewest matches given highest priority
Join and Append priority ranks back to the data and resort by priority
Select the 1st record for each reviewer
Sort by account number and reviewer. Use multi-row to identify duplicate account numbers that could be present. Filter them out.
Add 1 to running count to each reviewer’s count of assigned accounts
Merge remaining records back to run thru the loop again. The only records that are removed are the assigned accounts.
Macro is set for 500 iterations. You may need to change it depending on the volume of data.
All the priority methods are assumptions. Your company may have certain account classes or reviewer that will have different priorities. The priority between reviewer vs account when sorting is also a major assumption.
You may not get 100% of the accounts assigned depending on how you prioritize accounts, reviewers, and the combination of both. It was a trial and error method that changed as I added more restrictions to the reviewer. You can change them depending on your company’s requirement and update the missing accounts manually.
The I changed your data to fix the spelling of certain names and changed the column heading from Name to Reviewer since Name is used by the Transpose tool. I also added more Type2 and Type3 to reviewers to reduce the population of allowed accounts for each, thus making it a bit more complex to assigning the accounts.
I've carefully read ur whole workflow, and i think it is a fantastic job, and the result is exactly what i want.
However, i tried to make fully understanding about ur solution since maybe we will have some imporvements in different circmunstances.
My main painpoint comes from the Macro design, especially about the relationship between the 2 workflows u sent to me, and the linkage between 2 workflows, cuz i've never used Macro like that befrore, i feel at a loss about the Macro workflow.
Would you plz kindly give furether advise & suggestion about my following concern:
1. Does the logic u designed just use 2 different types of Macro button, one is blue--iterative and another one is orange--Count records?
2. Does the Macro workflow u sent to me mean the inner logic about the Iterative Macro button (the blue one) and the Orange Macro button does not need any logci design?
3. Where doese the Macro input data come from? and how can i maintain in the future if any difference occur?
Thank you so much for your help, and if you could also send me some learning material linkage about the Macro used like this way, that would be amazing! Since I found myself still like a litle baby about the Macro solution.
The orange count records is a tool in the Transform menu. Some tools are set up as macros. I did not create that macro.
I designed the workflow behind the blue macro. Right click on the blue button and you'll have the option to open it and then modify it.
The macro input comes from the original workflow. See above if you are asking about seeing inside or modifying the macro.
I'm definitely not an expert and still learning about apps and macros. I'd suggest looking at the Training menu and find the macro subjects. You can also use the search bar and look for articles and solutions related to macros. Designer has sample macros with great explanation in the Help/Sample Workflows/Macros area. Additionally I'll google it and will find an appropriate link to an article or video. Feel free to reach out if you have questions on the specifics of the workflow or macro. Hope that helps! Good luck!
I've managed to build the logic myself with my real case.
However, i've got a question about the logic u built to me, it's still about the Macro.
Inner logic of Macro
I found that, the in put file shown in the yxmd. workflow is about 1,8k info, but when I open the Macro workflow, it just shown the input file fo excat 100 rows, i'd like to know where did the input file come from in the Macro workflow about the blue button, i mean, i knwo where to type in the info, but i don't konw how did these info selected.
I found it's a real challenge, looking forward to ur reply, thank you so much, Alex!