Clear allocation of two Data sets

[PatBr]

Hey everyone,

Article	Passage	Shelf
GLD1184080	405	D
JIN0233130	403	A
MHG0094574	405	B/C
RSG3428240	405	D
NBF0215240	403	D
NGS0143240	403	D
HSJ0054230	406	B/C
CLD0856	406	A

Location	Passage	Shelf
VZN-CDR-405-0147-D	405	D
VZN-CDR-405-0148-A	405	A
VZN-CDR-405-0148-D	405	D
VZN-CDR-406-0401-A	406	A
VZN-CDR-406-0401-D	406	D
VZN-CDR-406-0407-A	406	A
VZN-CDR-406-0407-C	406	C
VZN-CDR-406-0407-D	406	D
VZN-CDR-406-0408-A	406	A
VZN-CDR-406-0408-B	406	B

Above you can see small parts of two data sets. The top one descibes acticles that we need to sort into their designated storage spaces. At first the article number, following the passage the articles should be sorted in and the shelf level (A, B, C, D) within the passage in which it should get placed. Below you can see the storage spaces which are free following the same logic as above. When we try to allocate articles to free spaces, alteryx likes to duplicate these articles to all storage spaces it can possible fit in.
Since only one item can fit in one storage space, the slot and article should be blocked up after.
Does someone know a way how to assign the articles to only one storage space that fits and also then lock up that space so the next article goes into another shelf? In addition to that, when no fitting storage space is available within the right shelf (e.g. D) then it should get assigned to the next best one (e.g. C, then B, and A)?

[OTrieger]

@PatBr 
Hello,

There are different ways how to do it, some simpler and some more complex. The most important things is to built one workflow that matching your current knowledge in Alteryx even if not the most efficient workflow that can be created.

Logically how would you approach this situation? How would you do it logically not necessarily in Alteryx? Then assign the tool that you do that job.

What will be your fist thing that you will want to know to get this issue solved?

[Deano478]

@PatBr What kind of output are you trying to achieve here can you show us?

[Raj]

@PatBr 
it will be great if you can add an output as well.

[PatBr]

Hello OTrieger and Deano478,

I'm sorry, I wasn't really detailed in my inquiry.

It is supposed to look something like the following.

Location	Passage	Shelf	Article
VZN-CDR-405-0147-D	405	D	GLD1184080
VZN-CDR-405-0148-A	405	A
VZN-CDR-405-0148-D	405	D	RSG3428240
VZN-CDR-406-0401-A	406	A
VZN-CDR-406-0401-D	406	D
VZN-CDR-406-0407-A	406	A
VZN-CDR-406-0407-C	406	C	HSJ0054230
VZN-CDR-406-0407-D	406	D
VZN-CDR-406-0408-A	406	A	CLD0856
VZN-CDR-406-0408-B	406	B

If the article is supposed to be sorted into the passageway 406 and the shelf level A, it's supposed to combine the first free location in the passageway 406 on the shelf level A.

My first idea is to sort the article by passageway and shelf level. With the multi-row formular I count every article that's supposed to be sorted in i.e. passageway 405 and shelf level D by grouping by passageway and shelf level. The same I do for the locations. After that I can join them. I added some screenshots.

My real problem starts with the articles that couldn't be sorted, because I would need x iterations to go through until I sorted every article to a free location. It's supposed to go like this:

1. If the article couldn't find an empty location in it's designated shelf level it's supposed to change to another and try again. The levels are supposed to be checked for empty locations from B to C to A to D.

2. If there's no empty location in the passageway left. It's supposed to try the passageway that comes before the planned passageway (i.e. if 405 is full, then 404) and so on.

3. If there's no free location in any of the preceding passageways, it's supposed to check the later passageways that come after the originally planned one. Always checking the levels from B to C to A to D. The passageways range from 401-408.

Is there a way for someone like me who hasn't worked with iterative macros yet to build one that's easy to understand? Because that's the only idea I come up with when thinking about how to proceed.

Thanks for your help.

[OTrieger]

This might help you.

https://knowledge.alteryx.com/index/s/article/Getting-Started-with-Iterative-Macros-1583461640389