Looping or Multiple Rows

[CKyrle]

Hi guys, 

I am very new to Alteryx (in fact, I started yesterday) and I am running into a challenge for which I would be very glad to receive help. 

In my data below, I need to iterate (loop?) through every cluster and identify to which degree I can meet the required amount of cluster articles. For each cluster there is a varrying number of corresponding material IDs in stock. I want to meet the demand (required amount) via waterfall principle, assigning the available amount of material IDs from top to bottom, progressing to the next row as soon as the available amount of the material ID is expired, and also progressing to the next cluster as soon as the demand is met or the available amount is expired. 

Current table:

Cluster	Material ID	Required Amount of Cluster by Customer	Available Amount of Material ID in Storage
AAAAAA	111111	100	60
AAAAAA	222222	100	30
AAAAAA	333333	100	10
AAAAAA	333333	100	10
BBBBBB	999999	200	400
BBBBBB	888888	200	100
CCCCC	555555	50	0

Desired table:

Cluster	Material ID	Required Amount of Cluster	Available Amount of Material ID in Storage	Packaged Amount of Material ID	Remaining Amount of Material ID in Storage
AAAAAA	111111	100	60	60	0
AAAAAA	222222	100	30	30	0
AAAAAA	333333	100	10	10	0
AAAAAA	333333	100	10	0	10
BBBBBB	999999	200	400	200	200
BBBBBB	888888	200	100	0	100
CCCCC	555555	50	0	0	0

Of course I also want to address special cases like empty stock etc in a later point. 

I thought about using the multiple rows formula, but it doesnt really seem to be flexible or dynamic enough. 

Thank you very much in advance!!!

Best

[mceleavey]

Hi @CKyrle ,

Try the attached workflow, that should get you where you need to be.

Hope this helps,

M.