I hope someone can help me out with a particular problem. After several days processing data in a matrix form i am now stucked with it.
We have multiple products within multiple storages, and a list of orders, when (on which day) we have to deliver these products.
The warehousing is working with FIFO principle. If the oldest storage is out stock, we pick from the next later storage and so on.
I have to calculate now, from how many storage does a single product of one order come from. I have tried working with tranpose, crosstab and multiple rows, multiple fields but haven't successed jet. Below you can see the simulated datas. Ofcouse we have more then 6 storages and much more than 2 products.
Warehose Data
Object-Nr. | Storage place | Date of receipt | Stock |
1111 | A1 | 01.01.2020 | 100 |
1111 | A2 | 02.01.2020 | 100 |
1111 | A3 | 03.01.2020 | 500 |
1111 | A4 | 04.01.2020 | 300 |
1112 | A5 | 01.01.2020 | 350 |
1112 | A6 | 01.01.2020 | 200 |
Order list
Object-Nr. | Date of delivery | Delivery amount |
1111 | 10.10.2020 | 320 |
1111 | 11.10.2020 | 50 |
1111 | 12.10.2020 | 280 |
1112 | 10.10.2020 | 50 |
1112 | 11.10.2020 | 100 |
1112 | 12.10.2020 | 50 |
Solved! Go to Solution.
@NPham00
This is a very interesting topic in Alteryx. I believe we have similar weekly challenge for this.
Let try something and get back you later.
@NPham00
Can you see if this is what you want?
@Qiu Thank you very much for your effort.
I got it there too and i think we are very close to what i want.
The problem is it, f.e. after the delivery on "2020-10-10" of Objekt-Nr. "1111" 2 of the storages (A1 and A2) went out of stock. The 3.storage (A3) is decreased to 380. For the delivery on "2020-11-10" of the same Object-Nr. "1111" we have to pick from the 3.Storage. Then it would be decreased to 280.
And it goes on, based on how many deliveries you have...
The problem could be solved easily with VBA, when i create iterative loops through the field "Balance" and update it.
But i really need it solved by alteryx, since many of us are working with alteryx.
If you have any ideas, i would be very thankful.
@NPham00
Thanks for the comment. You are absolutely right. Let me work on it a bit more.
@NPham00
Any feedback?
If you do like the sample workflow, appreciate if you would mark it as accepted.
@Qiu Sorry for my belated reply, i'm now back in Office today.
You rock!!! It works perfectly. Thank you so much for all your efforts.
I have Zero idea of macro, but i tried to take a look at it.
The only Thing i couldn't understand, which part of your macro makes the Iteration?
The result in A Output seems still not working. And what does "C Output"?
I wish i could make this macro my own...