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 Cost07/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

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) The number of iterations should equal the number of itemIDs being passed to the macro by the summarise tool. Hope this helps 🙂 Cheers, FN

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.

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.

Loop though the data -recursive

prasad

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

AssignmentPlannedStartDate	IndividualItemID	PMComplianceDays	PMComplianceBanding	InternalTotalCost	ExternalTotalCost	PM Compliance Days (Revised)	Duration	Total Cost
07/09/2020 08:00	17NC1099	4	Above Target	197.7721		4	31/12/2020
10/01/2020 08:00	17NC1099	-8	Target	48		-8	05/05/2020 08:00
17/08/2020 00:01	17NC1099			115.125		1
10/07/2020 00:01	17NC1099			54.75		1
16/10/2020 00:01	17NC1099			46.125		1
07/07/2020 20:49	17NC1099				210	34
26/10/2020 00:01	17NC1099			54.75		1
15/01/2020 00:01	17NC1099			33		0
19/11/2020 00:01	17NC1099			63.375		1
27/07/2020 00:01	17NC1099			589.5		5
06/08/2020 00:01	17NC1099			1041.2128		5
25/11/2020 00:01	17NC1099			80.625		1
07/07/2020 15:20	17NC1099				2945.9	37
05/05/2020 08:00	17NC1099	0	Target	3		0	07/09/2020 08:00
21/09/2020 00:01	17NC1099			37.5		1
06/02/2020 00:01	17NC1099			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

Macros

Dynamic Processing

Data Investigation

Accepted answers

All comments

FredNajjar

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

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

Thanks,

Loop though the data -recursive.yxmd

prasad

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703