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

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

amcoville
5 - Atom

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!!

3 REPLIES 3
JoshKushner
12 - Quasar

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:

Iterate through rows join flow.PNG

 

 

Result Required Items:

End Required Items.PNG

 

Result Available Items:

End Available Items.PNG

 

 

Parse and Cast Dates:

Parse and Cast dates as 'Date' type.PNG

 

Date Overlap:

Date Overlap.PNG

 

Calculate Remaining Available Items:

Remaining Available Items.PNG

 

Update Available Items:

Update Available Items.PNG

 

Update Remaining Items:

Update Remaining items.PNG

amcoville
5 - Atom

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
12 - Quasar
Happy I could help! Please feel free to reach out if you have any more questions!
Labels