Nested Loops

[Jennell_McIntire]

There are various loops/control statements that can be used in a script to control script execution.  Some common ones include:

• If…then
• Do…loop
• For…next
• For each…next

It is also possible to use one loop/control statement within another loop.  In the script I am going to review in this blog, I used all of these control statements except the do…loop to generate the rank of a company, by region, over 8 years.  Let me begin by setting the scene and explaining what I am trying to do.  I had 8 years’ worth of company data that included the company's overall rank (in a Data table).  Let’s say the overall rank is the company’s rank based on sales across all regions for a given year.  My goal is to get a company’s rank for their specific region, each year, based on the overall rank.  For example, in the example data table below Company C may have an overall rank of 3 in 2017 but in its region, North America, it is the top company so it has a regional rank of 1.

Example Data

Year Company Overall Rank Region Regional Rank

2017	Company A	1	Europe	1
2017	Company B	2	Europe	2
2017	Company C	3	North America	1
2017	Company D	4	Central America	1
2017	Company E	5	North America	2
2017	Company F	6	Europe	3
2017	Company G	7	Europe	4

What I would like to do via the script is determine the regional rank for each company, each year, so I can use it in a KPI object in an app.  Here is the script that I am using to accomplish this:

Let’s step through the script step by step.

1. First, I use a For each…next loop to loop through each region (line 1). 
2. Then I use a For…next loop to loop through the years (line 3).  Since I knew what years of data I had, I hard-coded the years but you can also use variables for the years which is a good idea if the years may vary. 
3. My next step is to use an If…then statement (line7) to determine if this is my first time through the loop.  If it is the first time, year will be 2010 and the region will be Africa so this is what I check for in the If...then statement.  If this is the first time through the loop, I create a Temp table that has the key field, CompanyRegionKey, and the field, Regional Rank, which I calculate using the RowNo() function.  The RowNo() function returns the position of the current row in the resulting table starting at 1.  Since I am sorting the table by Rank, the rows will be added to the table in the regional rank order.  You can learn more about the RowNo() function in my RecNo or RowNo? blog.  If this is not the first time through the loop, then I create the Temp table and I use the noconcatenate prefix so that the data is not concatenated to the already existing Temp2 table (which has the same fields). 
4. My next step is to create (line 26) or concatenate to (line 29) the Temp2 table which will store the key field and the new Regional Rank field. 
5. After this step, I Drop the Temp table (line 32).  I remove the Temp table from the data model and recreate it every time I loop through the script so that RowNo() always starts at 1.  If I did not delete the Temp table and concatenated the data to it, the RowNo() function will pick up where it left off instead of starting over at 1.
6. For each region, I loop through all the years and create and save the Regional Rank.  Once all the years are complete for a region, the script will go to the next region in the For each…next loop. 
7. Once all regions and years are complete, I Left Join the Regional Rank field to my Data table and then Drop the Temp2 table from the data model.  I now have a Regional Rank value for each company in my Data table.

Save yourself time and lines of script by using loops when you need to repeat one or a series of statements in your script.  Loops can be nested, as I did in the script above, or used one at a time.  Instead of repeating the same statements for each year and each region, use nested loops so the statements in the script only need to be written once.  This also makes maintenance easier.  In the future, if a change needs to be made to the script, it only needs to be done once.  Check out other blogs about loops with Henric Cronstrom’s blog on Loops in the Script and my blog on the Do…Loop.

Thanks,

Jennell