Iterate through rows join to another table and update both tables before next iteration

[amcoville]

Greetings Alteryx Community!

I'm very new to Alteryx and have only just started diving in but even so I don't believe this question to be too simple.

My use-case/scenario is as follows (mock data):

Table 1:

CustomerID	Code	StartDate	EndDate	Required Items
1	PET	10/01/2017	10/07/2017	2
1	FAK	10/01/2017	10/07/2017	1
1	PET	10/22/2017	10/28/2017	4
1	FAK	10/22/2017	10/28/2017	2
2	PET	10/22/2017	10/28/2017	1
3	FAK	10/01/2017	10/07/2017	2

Table 2:

CustomerID	Code	StartDate	EndDate	Available Items
1	PET	10/01/2017	10/31/2017	5
1	FAK	10/01/2017	10/31/2017	3
3	FAK	10/01/2017	10/07/2017	3

The expected output is:

Table 1:

CustomerID	Code	StartDate	EndDate	Required Items
1	PET	10/01/2017	10/07/2017	0
1	FAK	10/01/2017	10/07/2017	0
1	PET	10/22/2017	10/28/2017	1
1	FAK	10/22/2017	10/28/2017	0
2	PET	10/22/2017	10/28/2017	1
3	FAK	10/01/2017	10/07/2017	0

Table 2:

CustomerID	Code	StartDate	EndDate	Available Items
1	PET	10/01/2017	10/31/2017	0
1	FAK	10/01/2017	10/31/2017	0
3	FAK	10/01/2017	10/15/2017	0

Essentially what I need to do is take each row of Table 1 (in date order) and check Table 2 (matching on CustomerId & Code & date range overlap) for available items. Then I need to use the appropriate number of available items from Table 2 to fulfill the necessary required items in Table 1. Both tables need to be updated before proceeding to the next row (as the available items cannot be used more than once for subsequent rows). If no record exists in table 2 then all the required items remain.

Is this possible in Alteryx? I've read some posts regarding Batch Macros for the iterative piece but I'm not sure how to accomplish what I'm trying to do within the macro.

Any help is appreciated and let me know if my question is not clear, I'm finding it hard to explain.

Thanks!!

[JoshKushner · Accepted solution]

You can actually achieve this without an iterative macro using a few multi-row formula tools. In my solution I renamed 'Table 1' to 'Required Items' and 'Table 2' to 'Available Items'.

Hope this helps!

Flow:

Result Required Items:

Result Available Items:

Parse and Cast Dates:

Date Overlap:

Calculate Remaining Available Items:

Update Available Items:

Update Remaining Items:

[amcoville]

Josh,

Thank you so much for this! In some ways I oversimplified my mock data, so the solution you provided won't work completely out of the box for me but it is more than acceptable. You provided more than I expected and were a great help. This was a component of a larger workflow, so I'm not all the way through yet but I'm optimistic I'll be able to resolve this with the method you suggested.

P.S. Sorry for the belated response.

[JoshKushner]

Happy I could help! Please feel free to reach out if you have any more questions!