How to write a nested loop in Ironpython for two columns in Spotfire which are cascaded.

[Prateek Rawat 3]

Hi all,

I want to iterate through the values of region column in the outer loop and then through country column in an inner loop(to basically read the countries within a region) and then for each combination of region and countries I want to export visuals to ppt or pdf.
Can anyone help me with the code snippet for looping through values of two cascaded columns?

Regards,
Prateek  

[Heleen Snelting]

Hi @Prateek Rawat 3 - did you already check out the various examples here https://community.spotfire.com/articles/spotfire/ironpython-scripting-in-spotfire/? Did that give any inspiration on how to solve it? 

Heleen

 

[Jose Leviaguirre]

Hello @Prateek Rawat 3

I am not sure if you need a script to export all the countries by region if you use the new forEach feature from the Automation Services Job Builder. @Atheer Al Attar explain this feature very well here in which you can execute a task, data function or script for each report you need by sending parameters.
 

If you still need to loop countries for each region, here is a script that might help. Consider the following dataset

Region|Country|Metric1|Metric2|Metric3
North America|USA|100|200|300
North America|Canada|150|250|350
Europe|Germany|200|300|400
Europe|France|180|280|380
Asia|China|220|320|420
Asia|India|210|310|410
South America|Brazil|160|260|360
South America|Argentina|140|240|340

Then you can iterate through each country region
 

from collections import defaultdict

# Assuming 'dataTable' is your data table in Spotfire with 'Region' and 'Country' columns
dataTable = Document.Data.Tables["Data Table"]

# Get references to the Region and Country columns
region_column = dataTable.Columns["Region"]
country_column = dataTable.Columns["Country"]

# Dictionary to store regions as keys and lists of countries as values
region_country_map = defaultdict(set)

# Get the selected rows based on filtering
rows = Document.ActiveFilteringSelectionReference.GetSelection(dataTable).AsIndexSet()

# Populate the dictionary with regions and their respective countries
for row in rows:
    region = region_column.RowValues.GetFormattedValue(row)
    country = country_column.RowValues.GetFormattedValue(row)
    region_country_map[region].add(country)

# Print each region and its associated countries
for region, countries in region_country_map.items():
    for country in countries:
        print(region,country) #Here is where you apply filters and export to pdf or ppt for every variation

Now that you have each region and country in the loop, you can apply the filter and export each value to pdf

[Atheer Al Attar]

Please take a look at the video @Jose Leviaguirre shared above.