loopHi,
I have a very specific problem in Alteryx.
I have two tables:
Table 1:
Cost_to_be_added
200
130
120
100
98
55
54
54
50
30
25
24
18
10
9
8
2
1
1
Table 2:
Salesman | Cost |
Bob | 30 |
Robin | 35 |
Ted | 36 |
Lilly | 40 |
Tracy | 45 |
Now I would like to add the costs of table 1 to the row in table 2 who has the lowest cost. So that the first entry of table 1 '200' will be added to Bob. So the table looks like this now:
Calculated Table (First iteration):
Table 2:
Salesman | Cost |
Bob | 230 |
Robin | 35 |
Ted | 36 |
Lilly | 40 |
Tracy | 45 |
After this addition, we check who now has the lowest cost. This will be Robin.So we take the next value from Table 1: '130' and add it to Robin's cost.
So now the table will be:
Calculated Table (Second iteration):
Table 2:
Salesman | Cost |
Bob | 230 |
Robin | 165 |
Ted | 36 |
Lilly | 40 |
Tracy | 45 |
Again we check for the person with the lowest cost and add the third value of table 1 to his/her cost. This process repeats until all the values of Table 1 has been added to Table 2.
This can be achieved simply using some programming language but as a part of my work, I've to implement this in Alteryx.
Any suggestions would be appreciated.
Solved! Go to Solution.
Hint: you're looking for an iterative macro.
cheers,
mark
Hi,
I looked into iterative macros but how do I remove the values from Table 1 that has already been used in the first iteration?
Thanks,
Sudeshna
In each iteration you can output a temp file where you s sample and ship the first record.
cheers,
mark
ps on my phone now. One day when I can run alteryx from an iPhone I'll be dangerous.
Hi,
Thanks a lot! This worked.
But can you explain what the part shown in the image attached is doing? I can understand that it is picking out the last iteration where all the Salesman names are present (ie count =5) but if after that iteration, any values for Cost changes, how will that be picked up?
Thanks,
Sudeshna
This part is to find last iteration(19th iteration in this example) and to eliminate other unnecessary iterations records. This part makes the workflow dynamic.
I've an added layer of problem now. There is an extra field in both he table: "Team" and I've to run this entire process for each of the teams separately. How to do that without manually running it after separating the files by teams?
Possible to share sample data?
Hey,
I figured it out. Turns out I had to convert the main workflow into another iterative macro with minor changes. So now it's one iterative macro inside another (like a nested loop).