ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Loop though the data -recursive

prasad
5 - Atom

Hi Experts

I have to build a login that will first 

 

1) Pick the rows that have PM compliance banding populated. (Let's call it PM Compliance Record)

2) For that row traverse through all rows which have the same individual item id but PMComplianceBanding is null.  (Lets call it non PM compliance records)

3) For  the "non PM compliance records" where assignmentplannedstartdate is between AssignmentPlannedStartDate and duration for PM Compliance Record, take the running total for InternalTotalCost and ExternalTotalCost and put that value in the Total cost filed for the PM Compliance Record

 

AssignmentPlannedStartDateIndividualItemIDPMComplianceDaysPMComplianceBandingInternalTotalCostExternalTotalCostPM Compliance Days (Revised)DurationTotal Cost
07/09/2020 08:0017NC10994Above Target197.7721 431/12/2020 
10/01/2020 08:0017NC1099-8Target48 -805/05/2020 08:00 
17/08/2020 00:0117NC1099  115.125 1  
10/07/2020 00:0117NC1099  54.75 1  
16/10/2020 00:0117NC1099  46.125 1  
07/07/2020 20:4917NC1099   21034  
26/10/2020 00:0117NC1099  54.75 1  
15/01/2020 00:0117NC1099  33 0  
19/11/2020 00:0117NC1099  63.375 1  
27/07/2020 00:0117NC1099  589.5 5  
06/08/2020 00:0117NC1099  1041.2128 5  
25/11/2020 00:0117NC1099  80.625 1  
07/07/2020 15:2017NC1099   2945.937  
05/05/2020 08:0017NC10990Target3 007/09/2020 08:00 
21/09/2020 00:0117NC1099  37.5 1  
06/02/2020 00:0117NC1099  80.625 1  

 

As an example for PM record with assignment planned date of 10/01/2020 and duration of 05/05/2020, I should pick 2 non PM records one with planned start date of 15/01/2020 and other with start date of 06/02/2020. Then take the sum total of cost and put 113.625 ( 30 + 80.625) in the total  cost for PM record with assignment planned start date of 10/01/2020 08:00

 

Any help will be appreciated. I thought  of using iterative macros but not sure how will I exit the loop or not have Cartesian product of the records

 

FredNajjar
8 - Asteroid

Hi @prasad 

 

Firstly, I assume the ( 30 + 80.625)  is a type and it should be 33+80.625... 

 

Secondly, an iterative macro could be a good solution for your use case, but it's overkill unless you have hundreds of IndivudalItemsIDs in your data source, which then a batch macro would be a much better approach from a data processing and performance perspective. 

 

The solution below would work perfectly fine as a batch macro, in case you have plenty of IndividualItemIDs 

FredNajjar_0-1619603018352.png

 

 

PS: Please make sure to mark this post as resolved, if you found my solution useful.

 

Thanks,

FN 

 

 

 

 

prasad
5 - Atom

Hi FN

 

Thank you for your reply. I have 1000s of inventory where this logic has to be repeated. I have put only one subset in the question and have around 200K of records to process.

 

How should I do that in either batch macro or iterative macro. If you could give some guidance it will be great help

 

FredNajjar
8 - Asteroid

Hi @prasad  

 

Here is the solution with the Batch macro. 

 

I have unioned the data to add two additional projects and multiplied the internal cost for project 17NC1100 by 2 and 17NC1101 by 3 (so you can see the difference) 

FredNajjar_0-1619605516334.png

 

 

PS: Please make sure to mark this question as resolved, if you found my solution useful. 

 

Thanks, 

FN 

apathetichell
13 - Pulsar

Have you watched the Interactive Lessons video on Iterative Macros under the Learn tab on this community? Iterative Macros are the closest Alteryx comes to recursion (unless you do it in the python/r tool etc.). With a bit of practice they become fairly straight forward. But having said that - I don't think your case calls for an iterative macro.

prasad
5 - Atom

Thank you FN. I have run the workflow it might take sometime to complete as it has close to 300k records 

 

I am trying to understand the function of batch macro. For the unique item Id passed through the control parameter does the batch macro process all the records for that item or does it do only sequentially ? 

 

Essentially in the workflow will the cross join get the full set of records for the item being processed. 

prasad
5 - Atom

Hi FN 

 

I don't think the batch macro worked. It seems that 58k records went to control parameter and the full population of data was 274k and the output is only 11 records so I am not able to understand how the macro processed it. 

 

Individually if I run only for batch of records for one item the logic works though.

 

 

 

prasad_0-1619630210379.png

 

FredNajjar
8 - Asteroid

Hi Parsad, 

 

Unfortunately, I can't answer your question without looking at the data. However, a batch macro will iterate through all records associated with IndividualItemIDs, one IndividualItemID at a time. In the example below, we have three IDs, therefore, we get three iterations (one iteration for each ID) 

FredNajjar_0-1619690469425.png

 

The number of iterations should equal the number of itemIDs being passed to the macro by the summarise tool. 

 

Hope this helps 🙂 

 

Cheers, 

FN 

Labels