Dynamic sum of all column

Question

question

Sebastien asked Nov 16, '18 Sebastien commented Nov 19, '18

Dynamic sum of all column

Hello !

I have a Global Table that gets more and more column during the simulation according to the number of product and with several rows. From this table I want the sum of all rows for each column. I tried SUM(*), which would be perfect but it does not work. Is there any way to achieve the same with one SQL-Request ? Is there any way to use INFORMATION_SCHEMA in Flexsim ?

Best regards

Software Version:

FlexSim 18.2.0

sql queries

· 3

Sebastian Hemmann commented · Nov 16, 2018 at 09:34 AM

Hi,

could you please share an example, why SUM does not work?

0 ·

Sebastien Sebastian Hemmann commented · Nov 16, 2018 at 10:01 AM

Hi,

SUM works only if I use it with a specific column, like SUM(Total). So to have the sum of all column I would need to do SUM(Total), SUM(Col2), SUM(Col3),...yet I do not know how many column I will have during the simulation. the concept of SUM(*) would work but this is not a proper request syntax.

For instance at the beginning of the simulation I have the following global table.

	Total
Factory 1
Factory 2

Then during the simulation the model updates dynamically the Global Table when one Sink receive a new Product. So athe end of the simulation I could have had 4 products which would make my Table look like that

	Total	Prod 1	Prod 2	Prod 3	Prod 4
Factory 1	14	2	3	4	5
Factory 2	30	6	7	8	9

And from this table I would like to sum on each column with a calculated table as follows. But I haven't succeed in doing that. I would love to have a SQL Request being able to do that without haveing to change the whole modell and how data are collected.

	SUM(Total)	SUM(Prod 1)	SUM(Prod 2)	SUM(Prod 3)	SUM(Prod 4)
Sum	14	8	10	12	14

0 ·

Steven Hamoen Sebastien commented · Nov 16, 2018 at 03:42 PM

@Sébastien B2 Isn't it easier if you execute the query everytime something comes into the sink? You then know what column to recalculate and you can create the SUM table on the fly.

0 ·

Answer 1 · 2018-11-16T19:05:00Z

Jordan Johnson answered Nov 16, '18 Sebastien commented Nov 19, '18

I would recommend a different schema for your table. If you use a schema like the following:

Factory	Product	Quantity
Factory1	Prod1	12
Factory1	Prod2	14
Factory2	Prod1	18

Then you can write many helpful queries. If you want to sum all products in all factories, you can use

SELECT SUM(Quantity) AS Total FROM MyTable

If you want the total per product, you can use

SELECT Product, SUM(Quantity) AS Total FROM MyTable GROUP BY Product

Or if you want the total per factory, you can use

SELECT Factory, SUM(Quantity) AS Total FROM MyTable GROUP BY Factory

If you choose to keep the same schema, there is no SQL query that will work for every model run. You could use FlexScript to run an analysis on the table:

Table myTable = Table("MyTable");
for (int r = 1; r <= myTable.numRows; r++) {
	int total = 0;
	for (int c = 1; c <= myTable.numCols; c++) {
		string header = myTable.getColHeader(c);
		if (header == "Total")
			continue;
		
		total += myTable[r][c];
	}
	myTable[r]["Total"] = total;
}

· 1

Sebastien commented · Nov 19, 2018 at 10:59 AM

Hello Jordan.

Thank you for your detailed answer. I was already thinking about changing the tables like you mentionned. But I would have had to change a lot of things in the model and because of that I wanted to know if there was another solution.

I could use a Flexscript solution although the one with a new Table arrangement would be more useful. I think I'll change the whole model as far as data collection is concerned. It also may be better for a later addition of functionnalities.

Thank you again for your help !

0 ·

question