Need some iteration assistance - Iterate Table

1

Hi,

I am having a somewhat difficult time trying to get this to work:

Input Tables:

  1. A list of 38 countries (single column with Country Code)
  2. A table with role rates by country (each country has 12 rows of data with list and cost rates for each
    role in local currency along with global list and cost rates in USD)
  3. A table with a list of model numbers that detail each role used in the model with a number of hours for
    that role (each row has the model number, number of trips included, role needed, and number of
    hours). Each model will have at least two roles but could have up to 12.
  4. A table that has just one row for every model with high level information.

What I need to do:

I need to produce an updated Table #4 with 152 additional columns (4 for each country from Table #1).

I am trying to figure out to iterate this:

For each row in Table #4:

  1. Get the matching detail rows from Table #3 (will be at least two, possibly more) and for each row:
    a) For each country in Table #1 calculate the Local List, Global List, Local Cost, Global Cost
    using the country and role to lookup the rates from Table #2
  2. Once the details have been calculated then aggregate the table to a single line grouping by model and
    and summing the 152 columns of pricing
  3. Return the result to the calling module and append them to the row

Once all Table #4 rows have been processed export them to an Excel workbook.

Here is what I can’t figure out:

How can I iterate through Table #4 without destroying the input columns?
How do I append the 152 columns based upon a table I can’t join because of no matching values?

For the life of me I can’t get this to work.

Any help would be greatly appreciated.

Thanks,

Keith

2

Thank you for the detailed description, Keith.

There are two ways how to iterate a table and have the row data in the iterated module:

Use the "Parameter table" action

If the table isn't too wide, then you can assign all its columns to the respective parameters of the iterated module. In the iterated module, use the "Parameter table" action to create a 1-row table with parameter values as columns.

Iterating table with itself

Alternatively, derive a table and use the "Iterate table" action to iterate the source table.
image

In the iterated module:

  • Create a parameter that would uniquely identify an item (or batch) from the dataset. Assign the parameter in the "Iterate table" action in the parent module.
  • Create the "Input" action. Click "Populate automatically" to fill it out with data from the parent module.
  • Use the "Parameter table" action to produce a table with this parameter.
  • Use the "Keep/remove matching" to filter only rows with the parameter value. Thus you will obtain only the necessary subset of data. You can also filter rows with an expression, but this would be slower.

image
image892Ă—449 6.04 KB

This method is suitable for tables of any width as well as for processing rows in batches.

You can either create a dummy key column (e.g. with "1") in both tables, merge by it, then deleted. Or, even simpler, use the "Append table" action in the "Append columns" mode.

3

Hi Dmitry - I am not sure I understand how to use the passed “country” parameter to build the new column names. My column names to append need to start with the “country” parameter value. How can I set the column name dynamically in the iteration?

4

Hi Keith,

Can you provide an example? A screenshot or a spreadsheet with sample data would be best.

5

Hi Dmitry,

Thanks for all your help. I managed to do this WITHOUT using “Iterate Table” by using a join column = 1 in all my tables which allowed me to flatten the data and create individual rows for each of the pricing elements and then used PIVOT to create the table I needed. I really appreciate all the help.

Thanks,

Keith

1 Like