community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

How to cycly allocate sampling result

Highlighted
Meteor

Hi all,

 

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:

  1. The reviewer cannot review the account belongs to the type which reviewer is responsible for. To be more specific which means:
    Account NoType
    Account 2A
    Account 13C
    Account 25F
    Acconut 150G
    Account 179D
    Account 190E
    Account 197A
    Tab1 shown some of the sampling results, amd the result should be allocated to the follwing reviewer name.
    NameTyep1Tyep2Type3
    AliceA  
    BenBD 
    ZoeACG
    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.
  2. 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.
  3. 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.

Thank you guys!

Looking forward to ur reply!

 

Regards,

Laky

 

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

Hi all, I've updated my Alteryx file.

 

Regards,

Laky

Bolide

your data is not included.  you'll need to attach the excel file or a packaged workflow if you can

Meteor

oh sure, sorry about that.

Meteor

Hi, I've also upload my excel original excel file, is this time ok?

 

Regards,

Laky

Bolide

Good Morning!

 

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.

reviewer.JPG

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.

 

Requirements

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

 

Workflow

  • 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

Macro

  • 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.

 

Assumptions:

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.   

Issues:

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.

Notes:

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.

reviewer2.JPG

 

Meteor

Hi Alex,

 

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.

Looking forward to ur reply!

 

Best regards,

Laky

 

Bolide

Glad you got to spend some time with it. 

  • 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!

Meteor

hi Alex,

 

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.

 

Capture.PNGBlue Bottun

 

 

 

 

 

 

 

Capture1.PNGInner 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.

 

Capture.PNG

 

I found it's a real challenge, looking forward to ur reply, thank you so much, Alex!

 

Best regards,

Laky

Bolide
It is a template and not the full data set. It is to map the input. I'm on vacation and without my laptop. I can try to answer questions, but don't have the workflow.

Sent from my iPhone
Meteor

Hum~, Alex,

 

This morning i tired to fix myself.

It seems succeeded, but which is just based 10 smaple size for testing purpose.

However, actually, my sample size has 21k befor using blue button, and it runs very very very slowly.

Any idea about that?

 

 

 

Capture.PNG

 

 

Thank you so much~

Enjoy ur holiday~

 

Best regards,

Laky

Labels