[SQL] I cannot select a specific row from a statistics collector.

Question

question

Sebastien asked Jun 06 2018 at 10:59 AM Sebastien commented Jun 08 2018 at 7:53 AM

[SQL] I cannot select a specific row from a statistics collector.

Hello everyone,

I have created a small model with 2 processors. I added a State Pie Chart and installed the template to be able to extract the total processing time for each processor.

Then I tried to do a calculated table to multiply the processing time by a value only for the processor 1. I wrote the following SQL request:

SELECT Processing FROM [Composite State Pie Collector] WHERE Object = 'Processor1'

Unfortunately just the title of the column appears. No data. I tried to use the same statistics collector and select the processing time where the idle was 0 for instance, and it worked. So i checked the type of the column object in the statistics collector and changed it from double to string. Yet it is not working. I tried using different quotation marks like 'Processor1' , "Processor1" and even ´Processor1´ but nothing works. I do not know where the problem is. Could anyone help me? Please find the model attached.

statecollector.fsm

Warm regards,

Software Version:

FlexSim 18.1.0

statistics collector sql

statecollector.fsm (24.9 KiB)

· 2

Arun Kr commented · Jun 06 2018 at 5:01 PM

Hi @ Sébastien B2,

Create another column in the statistics collector called name and display the name of the object.

Then you use the query it will work. Your modified query will look like this.

SELECT Processing FROM [Composite State Pie Collector] WHERE Name = 'Processor1'

Regards,sebastian-support.fsm

Arun KR

2 ·

sebastian-support.fsm (27.9 KiB)

Sebastien Arun Kr commented · Jun 07 2018 at 6:52 AM

Thank you for your answer. I chose to use the solution from Jordan as it fits more to my requirements.

Warm regards,

1 ·

______

Cookie preferences

Your privacy is important to us and so is an optimal experience. To help us customize information and build applications, we collect data about your use of this site.

May we collect and use your data?

Learn more about the Third Party Services we use and our Privacy Statement.

Strictly necessary – required for our site to work and to provide services to you

These cookies allow us to record your preferences or login information, respond to your requests or fulfill items in your shopping cart.

YES

Improve your experience – allows us to show you what is relevant to you

These cookies enable us to provide enhanced functionality and personalization. They may be set by us or by third party providers whose services we use to deliver information and experiences tailored to you. If you do not allow these cookies, some or all of these services may not be available for you.

YES

NO

Customize your advertising – permits us to offer targeted advertising to you

These cookies collect data about you based on your activities and interests in order to show you relevant ads and to track effectiveness. By collecting this data, the ads you see will be more tailored to your interests. If you do not allow these cookies, you will experience less targeted advertising.

YES

NO

Are you sure you want a less customized experience?

We can access your data only if you select "yes" for the categories on the previous screen. This lets us tailor our marketing so that it's more relevant for you. You can change your settings at any time by visiting our privacy statement

Your experience. Your choice.

We care about your privacy. The data we collect helps us understand how you use our products, what information you might be interested in, and what we can improve to make your engagement with Autodesk more rewarding.

May we collect and use your data to tailor your experience?

Explore the benefits of a customized experience by managing your privacy settings for this site or visit our Privacy Statement to learn more about your options.

Answer 1 · 2018-06-06T19:25:31Z

Jordan Johnson answered Jun 06 2018 at 7:25 PM Sebastien commented Jun 08 2018 at 7:53 AM

In the model you have attached, the Statistics Collector is storing the ID of the object in the first column. However, the ID is a number, and in the model, the Statistics Collector is storing that number as text. The number should be stored as a double, and that value should be formatted as Object.

Once you make those changes, you can then use the following query in your Calculated Table:

SELECT Processing FROM [Composite State Pie Collector] 
WHERE Object = StatisticsCollector.getID(model().find("Processor1"))

statecollector-fixed.fsm

statecollector-fixed.fsm (28.8 KiB)

· 3

Sebastien commented · Jun 07 2018 at 6:50 AM

Thank you for your answer ! It works great now.

I did not notice that the Object column changed to IDs. I did it again and first it contained a name (Processor1 and 2) although the column type is set to double. That is why I changed it to String afterwards and then the names were not displayed anymore but the IDs. I do not know why...

But then I have another problem how can I combine two queries into one ?

I would like to have somehting like this:

SELECT Processing*23/3600 AS Processor1 FROM [Composite State Pie Collector] WHERE Object = StatisticsCollector.getID(model().find("Processor1"))

AND

SELECT Processing*23/3600 AS Processor2 FROM [Composite State Pie Collector] WHERE Object = StatisticsCollector.getID(model().find("Processor2"))

in order to have a calculated table with 2 columns each coming from a different query. Is this possible ?

Warm regards,

0 ·

Jordan Johnson ♦♦ Sebastien commented · Jun 07 2018 at 4:43 PM

It sounds like you are taking data that is in multiple rows of a single column, and trying to put it in a single row with multiple columns, which is basically a transposition. FlexSim's SQL doesn't include support for transposition.

If it really is a query on two objects, you can use this to do what you want:

SELECT 
	SUM(Processing1_internal) AS Processing1,
	SUM(Processing2_internal) AS Processing2
FROM (
	SELECT 
		CASE WHEN Object = StatisticsCollector.getID(model().find("Processor1"))
		THEN  Processing*23/3600
		ELSE null
		END
		AS Processing1_internal,
		CASE WHEN Object = StatisticsCollector.getID(model().find("Processor2"))
		THEN Processing*23/3600
		ELSE null
		END
		AS Processing2_internal
	FROM [Composite State Pie Collector] 
)<br>

Note that this does scale well. The more objects you want, the longer and more complicated the query gets.

statecollector-fixed-crazy-query.fsm

0 ·

statecollector-fixed-crazy-query.fsm (29.3 KiB)

Sebastien Jordan Johnson ♦♦ commented · Jun 08 2018 at 7:53 AM

Thank you ! It works perfectly.

0 ·

question