Iterating throw rows and updating the existing columns in a table

[AmitojSingh]

Hi All

I have a peculiar problem i believe, and am not sure how should i solve this

I have a condition X

if X = True then I need to ignore the remaining rows and stops else

if X = False then I need to move to the next row,  update a field value based on a formula and then recalculate the main condition and check whether its True of False. If its True then i can stop else I need to folllowing the above steps again until my condition changes to True

I am not sure how or if I can update an existing field value whilst iterating through the rows and doing my condition checks

Hope this makes sense.

[Qiu]

@AmitojSingh 
Is it possible you provide a set of sample input and output so we can better understand your question?

[mceleavey]

Hi @AmitojSingh ,

can you post some mock data so we can understand better and build something based on your logic?

Thanks,

M.

[danilang · Accepted solution]

Hi @AmitojSingh 

What your describing here is the perfect situation for using a Iterative macro.   One of the conditions for deciding whether to use a an iterative macro or a batch macro is "Do the input records change between iterations". Since your process updates a row, calculates the stop condition and then starts over, the inputs will change from one iteration to the next

The details of the macro will depend on your data and the transformations that you do to the record and how you calculate the stop condition, but the attached sample demonstrates the general concept

The process starts by using a Filter to select the record that you want to modify.  In this case, the criteria uses Engine.IterationNumber to select the records one by one.  Engine.IterationNumber starts at 0 and increments by one every time the macro loops. 

Once the current record is selected, perform the operation that you need.  Here I'm just adding 10 to the value, but you would replace this with whatever sequence of tools is required to modify the current record.  Union all the records together and then drop down into the Pass/Fail criteria container.  Here I use a Summarize tool to get the sum of all the values.  The following formula evaluates the criteria and adds a new StopLooping flag based on the result.  If sum >=130 then StopLooping is true, else it's false.  The StopLooping flag is added to all the records using the Append tool.

The following filter is where the looping either continue or stops.  All the records with StopLooping = True are pass to the data output back to the main procedure.  All the records with StopLooping = False are passed to a special output which is used to feed records back into the input for the the next pass through the macro.  Since the Append tool add the StopLooping flag to all records, they either all pass and are returned to main workflow or they all fail and are sent back to be processed for another iteration.  

For more information about iterative macros and how they're configured, including how to specify which output is the iterative output, check out the interactive training videos

Dan 

[AmitojSingh]

Hi Everyone, Happy Friday, thanks everyone for your time and answers, I am still testing this solution and see if it works for me.

Have changed my problem statement slightly, as I am testing the following logic 

I have a specific trade date (1 date), i wanted to check that specific date against a list of static dates (holiday dates) and check whether that specific trade date is same as one of the holiday dates and if it is, i want to ideally increment my trade date by 1 and check that new trade date against my list of static dates (holiday dates), i want to continue doing this until my trade date is definitely not present in my list of static dates (holiday dates)

[AmitojSingh]

Hi there, I am trying to do the following

1. Have a specific value date which I need to check against a list of dates (holiday dates)

2. I do a join between these 2 datesets and if i find a match, I would like to increment the date by 1

3. Repeat steps 1 and 2 until my Value date is not same as the list of dates

I have attached the code, I am trying to understand if we can have 2 seperate inputs in a Iterative Macro

Many Thanks for your help

Amitoj

[AmitojSingh]

Hi there, I am trying to do the following

1. Have a specific value date which I need to check against a list of dates (holiday dates)

2. I do a join between these 2 datesets and if i find a match, I would like to increment the date by 1

3. Repeat steps 1 and 2 until my Value date is not same as the list of dates

I have attached the code, I am trying to understand if we can have 2 seperate inputs in a Iterative Macro Many

Thanks for your help

Amitoj