Using getdatastat() with global table

Question

question

Noah Z asked Jun 16, '16 Phil BoBo edited Jun 16, '16

Using getdatastat() with global table

I have a global table with columns that have different quantities of numbers (i.e. column 1 has 300 values and column 2 has 450 values (these vary from run to run)) and I am trying to pull the mean of the columns but getdatastat() function pulls the whole column (all the zeros after the real data). Is there a way to tell getdatastat() to ignore cells with zero values?

(Original question referencing same model found here).

Software Version:

FlexSim 16.0.1

global table mean getdatastat ignore cells

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.

Answer 1 · 2016-06-16T16:23:45Z

Jordan Johnson answered Jun 16, '16 Phil BoBo edited Jun 16, '16

It is likely that you can use the query command to get what you want. You can use SQL aggregation functions (SUM, AVG, COUNT, MIN, MAX, STD, and VAR) to analyze column data.

If you want to ignore all zeros...

This is the easy case. Suppose your table looks like this one:

Notice that there are only zeros at the end of the data set. If that is the case for you, then the following code would work (valid in a script window):

query("SELECT AVG([Col 1]) WHERE [Col 1] != 0", reftable("GlobalTable1"));
return getqueryvalue(1, 1);

The first line runs the query (the first argument) on the target table (the second argument). The result of this query is a 1x1 table (internal to the query command), with the average of all values in Col 1, excluding the zeros. The getqueryvalue command retrieves data from the query command's internal results table.

If you want to only ignore zeros at the end of the set...

If you want to include some zeros in your calculation, but ignore others, the solution is a little trickier. While SQL has a LIMIT command, that limits the number of rows in the results table, not the number of rows included in aggregation functions.

However, the query command may still make your life easier. In the end, you will need to know how many rows each column has, so you can appropriately limit your tables. The following code may help:

// this node will store a temporary table
treenode tempTable = setname(nodeinsertinto(node("Tools", model())), "tempTable");
// Get only the values you want from a given column (5, in this case)
query("SELECT [Col 1] as c1 LIMIT 5", reftable("GlobalTable1"));
// put those values in the temporary table
dumpquery(tempTable);
// use the query command (or getdatastat) to analyze the tempTable
query("SELECT AVG(c1)", tempTable);
// get the result
double value = getqueryvalue(1, 1);
// remove the temporary table
destroyobject(tempTable);
// now you have the answer
return value;

The idea is to use the query command to get just the rows you want, and dump that 1-column table to a new, temporary table. Then you can analyze that table.

globaltable.png (11.8 KiB)

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.

Answer 2 · 2016-06-16T17:14:29Z

Phil BoBo answered Jun 16, '16 Phil BoBo edited Jun 16, '16

If you know that rows 1 - someValue are the valid values, you can just pass that value as the second parameter to getdatastat() instead of the number of rows in the table:

int numRowsToAverage = 300;
double average = getdatastat(STAT_MEAN, numRowsToAverage, gettablenum("GlobalTable1", count, column));

You can also use a loop to determine what the last row with a number other than 0 is:

int numRowsToAverage = gettablerows("GlobalTable1");
while (gettablenum("GlobalTable1", numRowsToAverage, column) == 0 && numRowsToAverage > 0) {
	numRowsToAverage--;
}
double average = getdatastat(STAT_MEAN, numRowsToAverage, gettablenum("GlobalTable1", count, column));

Or just find the average using a loop instead of getdatastat:

double sum = 0;
double count = 0;
for (int row = 1; row <= gettablerows("GlobalTable1"); row++) {
	double value = gettablenum("GlobalTable1", row, column);
	if (value != 0) {
		sum += value;
		count++;
	}
}
double average = sum / count;

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.

Answer 3 · 2016-06-16T15:44:59Z

Jeff Nordgren answered Jun 16, '16

Noah,

It seems like your question was answered by Brandon Peterson here. From his description, you could find the information you are seeking from a range within a global table. The only thing you might have to do first, it loop through the table to find out how many non-zero values there are and use that for your range (1 through number of non-zero values).

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.

question