Export Specific Column From Global Table to Excel for multiple replications?

Question

question

Noah Z asked Jan 15, '20 Noah Z commented Jan 20, '20

Export Specific Column From Global Table to Excel for multiple replications?

write-table-on-replication-completion.fsm example-of-desired-output.pngI am trying to pull out a specific column of data from a global table and write it to an excel worksheet upon finishing a set of runs with experimenter. My goal is a bit different from other similar type questions though in that I want to figure out a way to run Experimenter and have it write these specific table column values to the excel worksheet but place it within the worksheet in an orderly fashion by replication( writing to the next column one after the other as replications increase).

Words might not be the easiest way to explain what I'm going for so I've included an example model that generates new table values each time the model is run along with a spreadsheet screenshot for what I'm attempting.

Does anyone have a suggestion for the cleanest way to approach this?

Software Version:

FlexSim 20.0.0

global table experimenter export data

example-of-desired-output.png (51.1 KiB)

write-table-on-replication-completion.fsm (90.6 KiB)

· 2

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

tannerp commented · Jan 15, 2020 at 11:47 PM

Not sure if this could be useful, but you could try creating a Global Variable that could change based on the Experimenter scenario, then use this variable to change which column of the Global Table is written to.

0 ·

variable-table-column.png (36.2 KiB)

Noah Z tannerp commented · Jan 16, 2020 at 02:04 PM

Interesting idea! This would definitely be a good way to solve the problem if I'm saving data from one replication from each scenario run in the Experimenter.

My issue is more focused on trying to export specific data table columns from every individual replication of the same scenario. In that case, I don't think this solution would work.

0 ·

Answer 1 · 2020-01-16T20:56:33Z

Phil BoBo answered Jan 16, '20 Noah Z commented Jan 20, '20

In the Experimenter's On Replication End trigger, if you add the "Write to a Global Table" pick option and edit the code, you can see an example of how you can pass data from the child replication up to the main process.

The default code assumes you are writing one value from each replication into a table with rows and columns based on scenarios and replications.

In your case, you have 1 scenario and each replication has multiple values. So you can change the code to copy the entire column of data instead of a single cell.

Attached is your example model modified in this way. 24939-write-table-on-replication-completion-1.fsm

On End Replication:

double replication = param(1);
double scenario = param(2);
treenode childexpfolder = param(3);
Table table = Table("GlobalTable1");
string savedTableName = ownerobject(table).name + "S" + string.fromNum(scenario, 0) + "R" + string.fromNum(replication, 0);
if (!objectexists(childexpfolder)) { // End of Replication on the child process
	// copy GlobalTable1 into a temporary table to pass back to the main process
	treenode savedtables = assertsubnode(model().find("Tools/Experimenter"), "savedtables");
	treenode tablecopy = createcopy(table, savedtables);
	tablecopy.name = savedTableName;
}
else  // End of Replication on the main process
{
	// copy the values from the temporary table into the main process's table
	treenode savedTables = childexpfolder.find("savedtables");
	Table tableCopy = node(savedTableName, savedTables);
	table.setSize(max(table.numRows, tableCopy.numRows), max(table.numCols, replication));
	table.setColHeader(replication, "Replication " + replication);
	for (int row = 1; row <= tableCopy.numRows; row++) {
		table[row][replication] = tableCopy[row][1];
	}
}

Another option would be to collect this data in a Statistics Collector instead of a global table, and then use the Scenario Chart in a Dashboard to aggregate the data from the statistics collectors of each individual replication.

24939-write-table-on-replication-completion-1.fsm (92.8 KiB)

· 1

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Noah Z commented · Jan 20, 2020 at 03:55 PM

This works very well! Thanks Phil!

0 ·

question