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

looping

96sudeshnasen
7 - Meteor

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

Bob30
Robin35
Ted36
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

Bob230
Robin35
Ted36
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

Bob230
Robin165
Ted36
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.

 

9 REPLIES 9
MarqueeCrew
20 - Arcturus
20 - Arcturus

Hint:  you're looking for an iterative macro. 

cheers,

 

 mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
96sudeshnasen
7 - Meteor

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

MarqueeCrew
20 - Arcturus
20 - Arcturus

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. 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
ponraj
13 - Pulsar

Here is the sample workflow. Hope this is helpful. 

 

workflow2.PNGworkflow1.PNG

 

 

96sudeshnasen
7 - Meteor

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,

SudeshnaMacro_doubt.PNG

ponraj
13 - Pulsar

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. 

96sudeshnasen
7 - Meteor

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?

ponraj
13 - Pulsar

Possible to share sample data? 

96sudeshnasen
7 - Meteor

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

Labels