Pulling values from a concatenated string based on a set of conditions

[christopherKiener12]

Hi I posted dummy data showing Division, month, closures, and BK Number. What I am trying to do is the following: 

If Division is "Midwest", Month is "01" and closures is "4" then show 4 bk numbers. If Division is "Midwest", Month is "02" and closures is "2" then show 2 bk numbers excluding those that were already used for month 01. 

[DataNath]

Hey @christopherKiener12, was just about to reply to your other thread. This is what I've got so far. I don't have a load of time to look through and sense check it so I'll leave that to you. From a quick glance it looks to perform what you've asked.

[IraWatt]

Hey @christopherKiener12,

What is the logic we are following here? Is it the number of closures show that many BK numbers except for the ones in the previous month?

I had a look and there are no rows with Division is "Midwest", Month is "02" and closures is "2" 

[christopherKiener12]

Hey, this is perfect! Is there any way you can share this as a yxmd file for some reason it is not opening for me. 

[Luke_C]

Hi @christopherKiener12 

I think this is a duplicate thread (this isn't a small ask so be patient). Here's a solution, I made a batch macro (to process each division) with an iterative macro to allocate the BK numbers.

[DataNath · Accepted solution]

If you try and open the .yxzp then it should unzip the individual components, as I've had to package it to include the macro I built as it's a dependency.

Failing that, you can just plug the macro in yourself if that isn't working. Just right click the canvas and Insert > Macros and navigate to where it's saved.

Individual workbook and macro attached.

[Luke_C]

@DataNath Clever with the record ID use! I went the iterative approach

[DataNath]

Cheers @Luke_C - I was thinking of taking an iterative approach within the batch but didn't have a load of time. Nice to see the logic behind it and always love different ways of reaching an end goal in Alteryx!

@christopherKiener12 I have actually just noticed an issue and made a quick tweak - I was filtering out 0 closures early in the macro and so in theory, this would drop BKs which could otherwise be used in future months. I've filtered out later just before the BKs are assigned which should remedy this. Updated versions attached. Also, I'd recommend taking a look over Luke's response and marking it as a solution - this wasn't a quick task and they've also delivered what you asked!