Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Processing data in matrix - iterative loops through data fields

NPham00
8 - Asteroid

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 placeDate of receiptStock
1111A101.01.2020100
1111A202.01.2020100
1111A303.01.2020500
1111A404.01.2020300
1112A501.01.2020350
1112A601.01.2020200

 

Order list

Object-Nr.Date of deliveryDelivery amount
111110.10.2020320
111111.10.202050
111112.10.2020280
111210.10.202050
111211.10.2020100
111212.10.202050
7 REPLIES 7
Qiu
20 - Arcturus
20 - Arcturus

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

Qiu
20 - Arcturus
20 - Arcturus

@NPham00 
Can you see if this is what you want?Capture5.PNG

NPham00
8 - Asteroid

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

NPham00_0-1602676974230.png

 



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.

Qiu
20 - Arcturus
20 - Arcturus

@NPham00 
Thanks for the comment. You are absolutely right. Let me work on it a bit more.

Qiu
20 - Arcturus
20 - Arcturus

@NPham00 

Still much to learn but I think I get what you want.

Capture6.PNGCapture7.PNG

Qiu
20 - Arcturus
20 - Arcturus

@NPham00 
Any feedback?

If you do like the sample workflow, appreciate if you would mark it as accepted.

NPham00
8 - Asteroid

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

Labels