Iterative Macro to calculate Holiday hours

Question

Hi There,

I am trying to create an iterative macro that will compare the leaves taken by a person adjust the leave balance accordingly.

So we have a data with the details of leave taken by a person with start and end date of the leave and the hours utilized in the leave. These are identified by the employee ID provided in the field [NR] (see example below)

Leave Hours

And we have another data that lists all the leave hours that a person can use based on different category each having different expiration date (see the example below)

Leave balance

So I would like to create an iterative macro that would calculate the balance of leave hours each person has according to the leaves they have taken. If the leave hours they have taken exceeds the first category within the expiration date, then it should utilize the hours from next category and then adjust the balance accordingly.

I have created a macro, but it takes almost half an hour each time if I run with data of multiple persons. So I would like to know if there is an easier way to create the macro that would help me to achieve the same results with less time. Attached the macro for reference.

Thanks in advance,

Hanz

Loop.yxmc

Qiu · Answer

@hanz_e 
Have you resolved this issue? If not yet, maybe we can take a look. 😁

dataguyW · Answer

Sometimes it is faster to get your data setup in a way you can summarize.  Not sure if you can get a person, type of leave, hours computed but you can then go back through the results, join to the target/allowed hours and do your subtractions.   There are many ways to do it with an iterative loop, but loops take longer depending on how many iterations.  This may be the old-school database concepts burned into my head here, but it can be more efficient non-looping if possible.

FláviaB · Answer

Hi @hanz_e,

While this is not a straight solution, I recommend taking a look at this Apps and Macros Guide.

There are many resources you can dig in. Check out this knowledge base article, or you can learn more about applications of Iterative Macros from the solutions to these discussion threads:

* Converting a Batch Macro into an Iterative Macro

* Iterative Macro stop conditions best practices

* Iterative Loop inputs fields versus looping back

* Iterative Macro inside a Batch Macro

Also, there are a few blogs with Iterative Macro examples that you can read about. This blog contains a helpful step-by-step walkthrough of the process of creating an Iterative Macro. And this blog includes an Iterative Macro nested in a Standard Macro for building hierarchies.

Lastly, you can test your knowledge by working alongside this recorded training: Build Your First Iterative Macro.

I hope that helps!

Flávia