How do I perform iteration over a fixed range?
BeantwoordI need to generate a list of entries in a table, and based on the value in a specific cell in a spreadsheet (which I can read), I need to generate as many instances of rows (with different content). Say the cell had a value of 30, then I need to iterate from 1 to 30 and for each iteration be able to perform a calculation and add a row to a Spreadsheet using Chains. How would I go about that? The iteration on a group does not seem to allow the selection of a counter as input.
-
Hi Waldo, while the below method should get the job done of iterating n number of times defined by a number pulled from a Sheet, it is a roundabout way to achieve the result.
- Use the Workiva command Get Sheet Data to retrieve the iteration value (eg. 30)
- Set appropriate values for Spreadsheet ID and Sheet ID.
- Specify the cell that the value is in (eg. A1) - Use the Tabular Transformation command Extract Value to get the value from Step 1.
- Set Column Index = 1
- Set Row Index = 1 - Use the Tabular Transformation command Advanced Query to generate a list of number from 1 to the number extracted in step 2.
- Set any values for File and Table Name. You don't need them for this, but they are required to save the node.
- Create a query to generate the list of numbers. The following is an example where <value> should be replace with the value extracted in step 2.WITH counter AS (SELECT 1 AS startNum UNION ALL SELECT startNum+1 FROM counter WHERE startNum+1 <= <value>)) SELECT * FROM counter
- Use the File Util command Strip Lines to remove the header of the file generated in step 3.
- Set Lines to Strip = 1 - Use the Tabular transformation command Split File to create separate files for each value which can then be iterated over.
- Make sure to set Records per file = 1 - Finally, add a Group Command and enable it to iterate over the Split File Chunks generated in step 5.
0
Phew! Thanks for the suggestion.
0
How can I check logs for all the iterations or any one specific iteration separately for a command, let's say "import file into table" below?
I am using group command iterations using steps provided above to update 23 wdata tables. In case any one of them gives an error at the import step, I am not able to see the error for it in logs.
The log for Import File into Table states Result as success despite error in one of the iterations.
0
Hi Krishma, you can see the logs for each iteration by selecting the iteration in the drop downmenu of the logs panel. Below is an example screenshot with a red box around the dropdown menu.
0
Thanks Jeff. Both your comments helped a ton! Cheers!
0
Hi Jeff,
How can I set a multi-select integer variable for a query? I am trying to extract the variable values from a sheet but the query is getting any records. As the chain ran successfully I am not able to figure out the issue.
0
I'm not sure I fully follow the question. But if you're trying to get a value from the retrieved Sheet data and pass it to a Query parameter, you could use the Tabular Transformation command Extract Value to get the value.
0U moet u aanmelden om een opmerking te plaatsen.
- Use the Workiva command Get Sheet Data to retrieve the iteration value (eg. 30)
Opmerkingen
7 opmerkingen