I have had multiple attempts to get iteration to work for me with no success. I am doing a forecast analysis. I have 19k products; each forecast for the next 8 weeks; 150 submission dates over 3 years and 300k+ forecasts. I have a project that has a parameter called LagConst, which calculates the week starting date of all the forecasts’ prediction for, say, 4 weeks ahead (the lag), then compares that date with shipment data to find accuracy of forecast. All works good. But I want to automate the process to run sequentially through each lag and output the result. I think I am doing or not doing something dumb. Is there a really simple example?
The calculation of the date in the main project is a function in an ‘new column’ transformation:
Lag date = (Forecast submitted date)+(LagConst * 7) [7 being days in the week to get to next week starting date]. How do I get a module or another project to iterate the LagConst sequentially from 1 to 8?
Can include project if that helps
Hello John, and welcome to the Community!
In this case, you will probably need the “Generate sequence” action in order to generate a sequence from 1 to 8 which will be the possible values of LagConst. Then use the “Iterate” action to loop through the list and assign the value of LagConst with the sequential numbers.
If you haven’t read it yet, I highly recommend checking out our tutorial on iterations.
If you are still stuck with designing iterations, feel free to post the project.
Thanks for the welcome. My module is generating a sequence and - I think - passing that to LagConst, but without any result. What am I doing wrong?
I attach the projectSTP_ETL.morph (29.3 KB)
Apparently, you’re expecting your iteration to return concatenated results. Switch the “Iterate” action to the “Iterate and append results” mode, select the result table in the iterated module:
Thanks Dmitry, some progress but still getting these errors:
Error: Iteration of module “Main” failed on iteration #2.
Source: action “Iterate”, module “IterateThruLags”, table “Table 1”
Called module: module “Main”
Overridden parameters:
{LagConst} = 2
Error: Value ‘1’ missing in column [LagConst].
Source: action “Replace”, module “Main”, table “Parameters”
(I have been through the iterate tutorials, did not understand the looping process for a simple parameter)
I followed your instructions and got an output into the model of result for Lag1, but not for other values (without error messages). I restructured main module, and got the messages above. Again, I feel convinced I am doing something simple wrongly, but cannot find it!
rgdsSTP_ETL.morph (31.8 KB)
Error: Value ‘1’ missing in column [LagConst].
Source: action “Replace”, module “Main”, table “Parameters”
If you read the error description, you can see that the error occurs in table "Parameters" where you replace value 1 with 2 in column "LagConst". But since you're doing iterations, the value of parameter "LagConst" changes in each iteration. Therefore the value of column "LagConst" in that table changes too because the table is created using the "Parameter table" action. In the first iteration, the value of column "LagConst" in this table is 1, so the replacement works. But in the 2nd iteration, the value of this column is 2, so the replacement action can't replace anything and fails.
When debugging iterations, the rule of thumb is to set the parameters of the iterated module to the values that were assigned to them at the moment of iteration. Again, the error message tells you about this:
Error: Iteration of module “Main” failed on iteration #2.
Source: action “Iterate”, module “IterateThruLags”, table “Table 1”
Called module: module “Main”
Overridden parameters:
{LagConst} = 2
You can see that iteration failed when {LagConst} was assigned 2. So if you set it to 2, you will see how the module worked, where it failed, and what the table data looked like at the moment of failure.
To simplify debugging, we even automate setting parameters to the values that caused iteration to fail when you press the "Open module" button in the error message (see below):
PS. I suspect the table "Parameters" is not even needed for your calculations. It was probably left mistakenly when you you experimented with parameters and can be safely deleted together with the disabled "Iterate table" action.