Automation Professionals' Integration Toolkit Module

3rd Party Modules
, , ,
21

Share code and sample data (small sample).

  1. Sure. PIVOT doesn't care what the expression is.

  2. You can provide a list of column heading to start with, in the order desired. Getting the tag history query to go with that seems challenging.

22
Sample Data

"#NAMES"
"t_stamp","productlevel_at_sp","cap supply","downstream stop request","empty_bottles_present","empty_bottles_primied"
"#TYPES"
"date","L","L","L","L","L"
"#ROWS","25"
"2023-07-13 13:00:00.000","0","2","6","0","2"
"2023-07-13 14:00:00.000","0","5","5","1","1"
"2023-07-13 15:00:00.000","0","4","4","1","3"
"2023-07-13 16:00:00.000","0","3","4","0","1"
"2023-07-13 17:00:00.000","0","8","1","0","0"
"2023-07-13 18:00:00.000","0","3","7","0","0"
"2023-07-13 19:00:00.000","0","4","0","0","4"
"2023-07-13 20:00:00.000","0","7","2","0","2"
"2023-07-13 21:00:00.000","0","2","6","0","3"
"2023-07-13 22:00:00.000","0","3","2","0","0"
"2023-07-13 23:00:00.000","0","14","1","0","0"
"2023-07-14 00:00:00.000","0","3","4","0","1"
"2023-07-14 01:00:00.000","0","4","2","0","1"
"2023-07-14 02:00:00.000","0","8","0","0","1"
"2023-07-14 03:00:00.000","0","4","6","2","2"
"2023-07-14 04:00:00.000","0","4","2","0","1"
"2023-07-14 05:00:00.000","0","1","5","1","2"
"2023-07-14 06:00:00.000","0","6","2","0","1"
"2023-07-14 07:00:00.000","0","5","2","0","1"
"2023-07-14 08:00:00.000","1","0","1","0","3"
"2023-07-14 09:00:00.000","2","3","1","0","0"
"2023-07-14 10:00:00.000","1","1","1","1","3"
"2023-07-14 11:00:00.000","0","7","1","0","0"
"2023-07-14 12:00:00.000","0","2","2","2","2"
"2023-07-14 13:00:00.000","0","0","0","0","1"

The two empty bottle columns would be summed together.

I don't really have any working code at the moment, a project script is called and produces a dataset similar to that provided. A table's data property will be bound to that to be displayed to the user.

What I have so far that doesn't work (It's not close) is this:

view('Select * Pivot "productlevel_at_sp"[0] For "t_stamp"',{Root Container.Power Table.historicalData}
	 )
23

Something like this:

view(
	"Select min(t_stamp) As Started \n"+
	"Pivot sum(sum(empty_bottles_present), sum(empty_bottles_primied)) \n"+
	"For system.date.format(t_stamp, '_HH') \n"+
	"In ['_%02d' % ((h+7) % 24) for h in range(24)]",
	{../Source.props.data}
)

This works for me, after I deleted the extra row. You need to provide data for exactly the 24-hour time period, with no overlap on either end.

Some notes:

  • You can never use * with PIVOT, as it uses GROUP BY on the FOR expression.

  • Column names in datasets that start with digits create many problems, hence the underscore. You can fix the display of the table to not show that.

  • I used nested sums instead of adding the sums, in case either column was completely null.

  • Perspective needs a columns list to display nicely.

1 Like
24

Thanks, it's much closer than I was.

I should have mentioned this was in Vision, but I can easily correct the reference for the source data, so that's no big.

However, this results in a 1R X 25C dataset, instead of a 5R X 25C dataset, which is what I would expect.

It's a pretty good starting point, I'll use it and continue to work through this.

Thanks a lot I think you've made the wall much thinner for my head.

25

Why would it have five rows?

(You can only PIVOT one calculation per query.)

26

Each row is representative of a Stop Reason. The goal is to pivot the whole table where the columns become the rows except for the t_stamp column which becomes the new column headers.

The original dataset has 6 columns, the t_stamp column becomes the headers and so goes away, leaving 5 Rows.

27

To make that happen, you will need to restructure your source data to tall. Supply three columns:

  • t_stamp

  • location (product_level, cap_supply, et cetera)

  • qty

Then you group by location and pivot on sum(qty).

1 Like
28

I also noticed that min(t_stamp) gave me Double, not Date. Use system.date.fromMillis(long(min(t_stamp))) if that matters to you.

29

Thanks again for the help, was finally able to get this worked out. Final working expression:

view(
	'Select args[0][path[0].split("_")[0]]  As "Occurences"\n'+
	'Group By path.split("_")[0] \n' +
	'Pivot sum(value) \n' +
	'For system.date.format(timestamp,"_ha")[:-1] In ["_{}{}".format((h % 12)+1,"P" if 11 <= h < 23 else "A") for h in xrange(6,23+7)]\n' +
	'Order By Occurences',
	{Root Container.Power Table.historicalData}
	,asMap('productlevel','No Product',
			   'cap','No Caps',
			   'downstream','Packaging',
			   'empty','No Bottles'
			   )
)
1 Like
30

Heh. Didn't expect to see a V2 function mixed into a view() expression.

31

I've been playing with ways to do joins with these tools. Quickly ran into some conceptual problems, and some bugs:

  • Needed a new function, flatten(), that takes a list of lists and yields a flat(ter) list--just the first two levels of collapse to one, further nesting is retained. This is required to combine the output of nested iterators into a single list.

  • Redefined unionAll to take a single columnInfo, simplifying the signature and making it more friendly to computed/massaged column names and quantities. I may do something similar to selectStar().

  • Discovered that nesting these iterators was not as reliable as I thought. Specifically, the constification process was too naive when an inner iterator could be constified, based on which position it held in the expression hierarchy. Skull-cracker, that one.

Anyways, fixes and feature updates:

For Ignition v8.1+: v2.0.4.231971924

Examples:

Many:One Left Join w/ fallback value, via lookup()

Consider an application with a common set of error codes/reason codes/status codes that have table of descriptions. If the descriptions are held in a memory tag for use with lookup, a dataset of error codes/reason codes/status codes can be supplemented with the text column via an iterator. Consider these two sample datasets, events and messages:

"#NAMES"
"t_stamp","EventCode"
"#TYPES"
"date","I"
"#ROWS","7"
"2023-07-14 10:00:00.000","0"
"2023-07-14 10:05:52.692","1"
"2023-07-14 10:06:23.000","0"
"2023-07-14 10:17:31.854","2"
"2023-07-14 10:17:34.303","0"
"2023-07-14 10:31:47.551","1"
"2023-07-14 10:32:17.842","9"

"#NAMES"
"Code","Message","Severity"
"#TYPES"
"I","str","I"
"#ROWS","7"
"0","Running","0"
"1","Cycle Stop","1"
"2","Operator Stop","1"
"3","Safety Stop","3"
"4","Temperature Fault","2"
"5","Pressure Fault","2"
"6","Timeout Fault","2"

This expression:

selectStar(
	{this.custom.events},
	lookup({this.custom.messages}, it()['EventCode'], "*unknown*", 'Code', 'Message'),
	'Message',
	'str'
)

Yields:

"#NAMES"
"t_stamp","EventCode","Message"
"#TYPES"
"date","I","str"
"#ROWS","7"
"2023-07-14 10:00:00.000","0","Running"
"2023-07-14 10:05:52.692","1","Cycle Stop"
"2023-07-14 10:06:23.000","0","Running"
"2023-07-14 10:17:31.854","2","Operator Stop"
"2023-07-14 10:17:34.303","0","Running"
"2023-07-14 10:31:47.551","1","Cycle Stop"
"2023-07-14 10:32:17.842","9","*unknown*"

Many:Many Cross Join (unfiltered)

When an inner join expression is too complex for index/map lookup, the general solution is a cross join followed by filtering. Consider the above source samples with this expression:

unionAll(
  asPairs(
    columnsOf({this.custom.events}),
    columnsOf({this.custom.messages})
  ),
  flatten(
    forEach(
      {this.custom.events},
      forEach(
        {this.custom.messages},
        forEach(
          asPairs(it(1), it()),
          it()[1]
        )
      )
    )
  )
)

This creates a row for every combination of the two dataset's rows. Yielding this:

"#NAMES"
"t_stamp","EventCode","Code","Message","Severity"
"#TYPES"
"date","I","I","str","I"
"#ROWS","49"
"2023-07-14 10:00:00.000","0","0","Running","0"
"2023-07-14 10:00:00.000","0","1","Cycle Stop","1"
"2023-07-14 10:00:00.000","0","2","Operator Stop","1"
"2023-07-14 10:00:00.000","0","3","Safety Stop","3"
"2023-07-14 10:00:00.000","0","4","Temperature Fault","2"
"2023-07-14 10:00:00.000","0","5","Pressure Fault","2"
"2023-07-14 10:00:00.000","0","6","Timeout Fault","2"
"2023-07-14 10:05:52.692","1","0","Running","0"
"2023-07-14 10:05:52.692","1","1","Cycle Stop","1"
"2023-07-14 10:05:52.692","1","2","Operator Stop","1"
"2023-07-14 10:05:52.692","1","3","Safety Stop","3"
"2023-07-14 10:05:52.692","1","4","Temperature Fault","2"
"2023-07-14 10:05:52.692","1","5","Pressure Fault","2"
"2023-07-14 10:05:52.692","1","6","Timeout Fault","2"
"2023-07-14 10:06:23.000","0","0","Running","0"
"2023-07-14 10:06:23.000","0","1","Cycle Stop","1"
"2023-07-14 10:06:23.000","0","2","Operator Stop","1"
"2023-07-14 10:06:23.000","0","3","Safety Stop","3"
"2023-07-14 10:06:23.000","0","4","Temperature Fault","2"
"2023-07-14 10:06:23.000","0","5","Pressure Fault","2"
"2023-07-14 10:06:23.000","0","6","Timeout Fault","2"
"2023-07-14 10:17:31.854","2","0","Running","0"
"2023-07-14 10:17:31.854","2","1","Cycle Stop","1"
"2023-07-14 10:17:31.854","2","2","Operator Stop","1"
"2023-07-14 10:17:31.854","2","3","Safety Stop","3"
"2023-07-14 10:17:31.854","2","4","Temperature Fault","2"
"2023-07-14 10:17:31.854","2","5","Pressure Fault","2"
"2023-07-14 10:17:31.854","2","6","Timeout Fault","2"
"2023-07-14 10:17:34.303","0","0","Running","0"
"2023-07-14 10:17:34.303","0","1","Cycle Stop","1"
"2023-07-14 10:17:34.303","0","2","Operator Stop","1"
"2023-07-14 10:17:34.303","0","3","Safety Stop","3"
"2023-07-14 10:17:34.303","0","4","Temperature Fault","2"
"2023-07-14 10:17:34.303","0","5","Pressure Fault","2"
"2023-07-14 10:17:34.303","0","6","Timeout Fault","2"
"2023-07-14 10:31:47.551","1","0","Running","0"
"2023-07-14 10:31:47.551","1","1","Cycle Stop","1"
"2023-07-14 10:31:47.551","1","2","Operator Stop","1"
"2023-07-14 10:31:47.551","1","3","Safety Stop","3"
"2023-07-14 10:31:47.551","1","4","Temperature Fault","2"
"2023-07-14 10:31:47.551","1","5","Pressure Fault","2"
"2023-07-14 10:31:47.551","1","6","Timeout Fault","2"
"2023-07-14 10:32:17.842","9","0","Running","0"
"2023-07-14 10:32:17.842","9","1","Cycle Stop","1"
"2023-07-14 10:32:17.842","9","2","Operator Stop","1"
"2023-07-14 10:32:17.842","9","3","Safety Stop","3"
"2023-07-14 10:32:17.842","9","4","Temperature Fault","2"
"2023-07-14 10:32:17.842","9","5","Pressure Fault","2"
"2023-07-14 10:32:17.842","9","6","Timeout Fault","2"

Wrap the above (for your application) with a where() expression to implement the unusual join. Do note that this can be computationally expensive on large datasets.

3 Likes
32

Multi-key joins, or any many-to-many joins, or any join using multiple column values from the inner nested loop, require the inner dataset be grouped, and then used as a mapping to pluck out the "many" subset that applies to each outer row. Consider these sample datasets tests and samples:

"#NAMES"
"key1","key2","Description"
"#TYPES"
"I","I","str"
"#ROWS","5"
"1","1","Product 1 Test A"
"1","2","Product 1 Test B"
"1","3","Product 1 Test C"
"2","1","Product 2 Test A"
"2","2","Product 2 Test B"

"#NAMES"
"key1","key2","Sample","Value"
"#TYPES"
"I","I","str","D"
"#ROWS","14"
"1","1","Pressure","1.1"
"1","1","Temperature","305.2"
"1","1","Tension","44.2"
"1","2","Pressure","7.5"
"1","2","Temperature","-55.1"
"1","2","Tension","8.9"
"1","2","Torque","0.67"
"1","3","Temperature","98.6"
"1","3","Torque","12.3"
"2","2","Pressure","15.1"
"2","2","Tension","7.5"
"2","2","Torque","11.7"
"2","2","Strain","0.034"
"2","2","Temperature","81.0"

This expression performs a multi-key inner join:

unionAll(
  asPairs(
    columnsOf({this.custom.tests}),
    columnsOf({this.custom.samples})
  ),
  flatten(
    forEach(
      {this.custom.tests},
      forEach(
      	coalesce(
			coalesce(
				asMap(
					groupBy(
						{this.custom.samples},
						it()['key1'],
						it()['key2']
					)
				)[it()['key1']],
				asMap()
			)[it()['key2']],
			asList()
		),
        forEach(
          asPairs(it(1), it()),
          it()[1]
        )
      )
    )
  )
)

Note the use of coalesce() to provide an empty map as the fallback when the first key is missing in the inner dataset, and to provide an empty list (of rows) when the second key is missing. It yields this:

"#NAMES"
"key1","key2","Description","key1","key2","Sample","Value"
"#TYPES"
"I","I","str","I","I","str","D"
"#ROWS","14"
"1","1","Product 1 Test A","1","1","Pressure","1.1"
"1","1","Product 1 Test A","1","1","Temperature","305.2"
"1","1","Product 1 Test A","1","1","Tension","44.2"
"1","2","Product 1 Test B","1","2","Pressure","7.5"
"1","2","Product 1 Test B","1","2","Temperature","-55.1"
"1","2","Product 1 Test B","1","2","Tension","8.9"
"1","2","Product 1 Test B","1","2","Torque","0.67"
"1","3","Product 1 Test C","1","3","Temperature","98.6"
"1","3","Product 1 Test C","1","3","Torque","12.3"
"2","2","Product 2 Test B","2","2","Pressure","15.1"
"2","2","Product 2 Test B","2","2","Tension","7.5"
"2","2","Product 2 Test B","2","2","Torque","11.7"
"2","2","Product 2 Test B","2","2","Strain","0.034"
"2","2","Product 2 Test B","2","2","Temperature","81.0"

This similar expression performs the corresponding left join:

unionAll(
  asPairs(
    columnsOf({this.custom.tests}),
    columnsOf({this.custom.samples})
  ),
  flatten(
    forEach(
      {this.custom.tests},
      forEach(
      	coalesce(
			coalesce(
				asMap(
					groupBy(
						{this.custom.samples},
						it()['key1'],
						it()['key2']
					)
				)[it()['key1']],
				asMap()
			)[it()['key2']],
			asList(
				forEach(
					columnsOf({this.custom.samples}),
					asList(it()[0], null)
				)
			)
		),
        forEach(
          asPairs(it(1), it()),
          it()[1]
        )
      )
    )
  )
)

The fallback for the second map level is changed to deliver a one-row list of null values for each column. It yields this output:

"#NAMES"
"key1","key2","Description","key1","key2","Sample","Value"
"#TYPES"
"I","I","str","I","I","str","D"
"#ROWS","15"
"1","1","Product 1 Test A","1","1","Pressure","1.1"
"1","1","Product 1 Test A","1","1","Temperature","305.2"
"1","1","Product 1 Test A","1","1","Tension","44.2"
"1","2","Product 1 Test B","1","2","Pressure","7.5"
"1","2","Product 1 Test B","1","2","Temperature","-55.1"
"1","2","Product 1 Test B","1","2","Tension","8.9"
"1","2","Product 1 Test B","1","2","Torque","0.67"
"1","3","Product 1 Test C","1","3","Temperature","98.6"
"1","3","Product 1 Test C","1","3","Torque","12.3"
"2","1","Product 2 Test A",,,,
"2","2","Product 2 Test B","2","2","Pressure","15.1"
"2","2","Product 2 Test B","2","2","Tension","7.5"
"2","2","Product 2 Test B","2","2","Torque","11.7"
"2","2","Product 2 Test B","2","2","Strain","0.034"
"2","2","Product 2 Test B","2","2","Temperature","81.0"

Do remember that Perspective objects do not accept maps with arbitrary keys, so if using an intermediate value in a custom property, use one of the asPairs() formats.

2 Likes
33

Be aware that if you dynamically produce a join with all columns from two datasets, you will likely have one or more duplicate column names. When Perspective renders these, the JSON form will use the column values to the right, which may be null for left joins.

If you don't need to dynamically construct the column names and types, supplying constants will simplify the expressions considerably.

34

I'm trying to wrap my head around the forEach and I've ran into this NPE.

SampleData

"#NAMES"
"Occurrence","_7A","_8A","_9A","_10A","_11A","_12P","_1P","_2P","_3P","_4P","_5P","_6P","_7P","_8P","_9P","_10P","_11P","_12A","_1A","_2A","_3A","_4A","_5A","_6A","Last 8","Last 24","Threshold"
"#TYPES"
"str","D","D","D","D","D","D","D","D","D","D","D","D","D","D","D","D","D","D","D","D","D","D","D","D","I","D","I"
"#ROWS","4"
"No Bottles","0.0","9.0","3.0","1.0","1.0","0.0","5.0","0.0","2.0","0.0","7.0","0.0","1.0","0.0","1.0","1.0","8.0","4.0","1.0","8.0","6.0","4.0","1.0","0.0","23","63.0","6"
"No Caps","1.0","4.0","0.0","3.0","2.0","0.0","1.0","0.0","3.0","1.0","4.0","4.0","14.0","1.0","0.0","0.0","2.0","0.0","0.0","0.0","1.0","0.0","0.0","0.0","5","41.0","4"
"No Product","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","1.0","0.0","0.0","0.0","0.0","0","1.0","1"
"Packaging","10.0","5.0","8.0","6.0","4.0","4.0","3.0","4.0","4.0","2.0","7.0","5.0","4.0","2.0","3.0","5.0","1.0","0.0","6.0","0.0","1.0","6.0","6.0","8.0","12","104.0","5"

Expression:

forEach(
       {Root Container.Power Table.data},
       asMap(it())
)
Error 
14:08:48.924 [AWT-EventQueue-0] ERROR com.inductiveautomation.ignition.client.util.gui.ErrorUtil - null
java.lang.Exception: Error executing expression binding on
Stop Occurances.Root Container.Table.data
	at com.inductiveautomation.factorypmi.application.binding.ExpressionPropertyAdapter.runExpression(ExpressionPropertyAdapter.java:92)
	at com.inductiveautomation.factorypmi.application.binding.ExpressionPropertyAdapter.startup(ExpressionPropertyAdapter.java:113)
	at com.inductiveautomation.factorypmi.application.binding.DefaultInteractionController.setPropertyAdapter(DefaultInteractionController.java:219)
	at com.inductiveautomation.factorypmi.designer.property.configurators.ExpressionConfigurator.bind(ExpressionConfigurator.java:246)
	at com.inductiveautomation.factorypmi.designer.property.configurators.ExpressionConfigurator.tryCommit(ExpressionConfigurator.java:192)
	at com.inductiveautomation.factorypmi.designer.property.configurators.ConfiguratorMultiplexor$EditorParent.tryCommit(ConfiguratorMultiplexor.java:398)
	at com.inductiveautomation.factorypmi.designer.property.configurators.ConfiguratorMultiplexor.tryCommit(ConfiguratorMultiplexor.java:546)
	at com.inductiveautomation.factorypmi.designer.property.configurators.DynamicOptsDialog.doOK(DynamicOptsDialog.java:95)
	at com.inductiveautomation.factorypmi.designer.property.configurators.DynamicOptsDialog$1.actionPerformed(DynamicOptsDialog.java:64)
	at java.desktop/javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
	at java.desktop/javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
	at java.desktop/javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
	at java.desktop/javax.swing.DefaultButtonModel.setPressed(Unknown Source)
	at java.desktop/javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
	at java.desktop/java.awt.Component.processMouseEvent(Unknown Source)
	at java.desktop/javax.swing.JComponent.processMouseEvent(Unknown Source)
	at java.desktop/java.awt.Component.processEvent(Unknown Source)
	at java.desktop/java.awt.Container.processEvent(Unknown Source)
	at java.desktop/java.awt.Component.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Component.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
	at java.desktop/java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
	at java.desktop/java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Window.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Component.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.EventQueue.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.EventQueue$4.run(Unknown Source)
	at java.desktop/java.awt.EventQueue$4.run(Unknown Source)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.desktop/java.awt.EventQueue$5.run(Unknown Source)
	at java.desktop/java.awt.EventQueue$5.run(Unknown Source)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.desktop/java.awt.EventQueue.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
	at java.desktop/java.awt.WaitDispatchSupport$2.run(Unknown Source)
	at java.desktop/java.awt.WaitDispatchSupport$4.run(Unknown Source)
	at java.desktop/java.awt.WaitDispatchSupport$4.run(Unknown Source)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.desktop/java.awt.WaitDispatchSupport.enter(Unknown Source)
	at java.desktop/java.awt.Dialog.show(Unknown Source)
	at java.desktop/java.awt.Component.show(Unknown Source)
	at java.desktop/java.awt.Component.setVisible(Unknown Source)
	at java.desktop/java.awt.Window.setVisible(Unknown Source)
	at java.desktop/java.awt.Dialog.setVisible(Unknown Source)
	at com.inductiveautomation.factorypmi.designer.property.configurators.DynamicOptsDialog.showDialog(DynamicOptsDialog.java:163)
	at com.inductiveautomation.factorypmi.designer.model.VisionDesignerImpl.openBindingDialog(VisionDesignerImpl.java:1169)
	at com.inductiveautomation.factorypmi.designer.property.editors.bb.DynamicOptionsButton.actionPerformed(DynamicOptionsButton.java:37)
	at java.desktop/javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
	at java.desktop/javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
	at java.desktop/javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
	at java.desktop/javax.swing.DefaultButtonModel.setPressed(Unknown Source)
	at java.desktop/javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
	at com.jidesoft.plaf.basic.BasicJideButtonListener.mouseReleased(Unknown Source)
	at java.desktop/java.awt.AWTEventMulticaster.mouseReleased(Unknown Source)
	at java.desktop/java.awt.Component.processMouseEvent(Unknown Source)
	at java.desktop/javax.swing.JComponent.processMouseEvent(Unknown Source)
	at java.desktop/java.awt.Component.processEvent(Unknown Source)
	at java.desktop/java.awt.Container.processEvent(Unknown Source)
	at java.desktop/java.awt.Component.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Component.dispatchEvent(Unknown Source)
	at java.desktop/javax.swing.plaf.basic.BasicTableUI$Handler.repostEvent(Unknown Source)
	at java.desktop/javax.swing.plaf.basic.BasicTableUI$Handler.mouseReleased(Unknown Source)
	at com.jidesoft.swing.DelegateMouseInputListener.mouseReleased(Unknown Source)
	at java.desktop/java.awt.AWTEventMulticaster.mouseReleased(Unknown Source)
	at java.desktop/java.awt.AWTEventMulticaster.mouseReleased(Unknown Source)
	at java.desktop/java.awt.Component.processMouseEvent(Unknown Source)
	at java.desktop/javax.swing.JComponent.processMouseEvent(Unknown Source)
	at java.desktop/java.awt.Component.processEvent(Unknown Source)
	at java.desktop/java.awt.Container.processEvent(Unknown Source)
	at java.desktop/java.awt.Component.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Component.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
	at java.desktop/java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
	at java.desktop/java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Window.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Component.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.EventQueue.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.EventQueue$4.run(Unknown Source)
	at java.desktop/java.awt.EventQueue$4.run(Unknown Source)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.desktop/java.awt.EventQueue$5.run(Unknown Source)
	at java.desktop/java.awt.EventQueue$5.run(Unknown Source)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.desktop/java.awt.EventQueue.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.run(Unknown Source)
Caused by: java.lang.NullPointerException: null
	at com.automation_pros.simaids.expressions.AsMap.execute(AsMap.java:106)
	at com.inductiveautomation.ignition.client.expressions.ClientDynamicDispatchFunction.execute(ClientDynamicDispatchFunction.java:43)
	at com.inductiveautomation.ignition.common.expressions.FunctionExpression.execute(FunctionExpression.java:69)
	at com.automation_pros.simaids.expressions.IterationHelper.condSubstExpression(IterationHelper.java:215)
	at com.automation_pros.simaids.expressions.IterationHelper.preExecuteAll(IterationHelper.java:100)
	at com.automation_pros.simaids.expressions.SimpleIterator.execute(SimpleIterator.java:181)
	at com.inductiveautomation.ignition.client.expressions.ClientDynamicDispatchFunction.execute(ClientDynamicDispatchFunction.java:43)
	at com.inductiveautomation.ignition.common.expressions.FunctionExpression.execute(FunctionExpression.java:69)
	at com.inductiveautomation.factorypmi.application.binding.ExpressionPropertyAdapter.runExpression(ExpressionPropertyAdapter.java:83)
	... 102 common frames omitted

This is an example straight from the docs so I am not sure what I am missing, I would expect it to work.

I need to iterate through a datasets columns and sum the rows for each column to add a Totals row to the dataset.

EDIT: I'm on V2.0.3 (b231942018)

35

Must be one of the bugs I fixed. I get this on v2.0.4:

[
  {
    "Occurrence": "No Bottles",
    "_7A": 0,
    "_8A": 9,
    "_9A": 3,
    "_10A": 1,
    "_11A": 1,
    "_12P": 0,
    "_1P": 5,
    "_2P": 0,
    "_3P": 2,
    "_4P": 0,
    "_5P": 7,
    "_6P": 0,
    "_7P": 1,
    "_8P": 0,
    "_9P": 1,
    "_10P": 1,
    "_11P": 8,
    "_12A": 4,
    "_1A": 1,
    "_2A": 8,
    "_3A": 6,
    "_4A": 4,
    "_5A": 1,
    "_6A": 0,
    "Last 8": 23,
    "Last 24": 63,
    "Threshold": 6
  },
  {
    "Occurrence": "No Caps",
    "_7A": 1,
    "_8A": 4,
    "_9A": 0,
    "_10A": 3,
    "_11A": 2,
    "_12P": 0,
    "_1P": 1,
    "_2P": 0,
    "_3P": 3,
    "_4P": 1,
    "_5P": 4,
    "_6P": 4,
    "_7P": 14,
    "_8P": 1,
    "_9P": 0,
    "_10P": 0,
    "_11P": 2,
    "_12A": 0,
    "_1A": 0,
    "_2A": 0,
    "_3A": 1,
    "_4A": 0,
    "_5A": 0,
    "_6A": 0,
    "Last 8": 5,
    "Last 24": 41,
    "Threshold": 4
  },
  {
    "Occurrence": "No Product",
    "_7A": 0,
    "_8A": 0,
    "_9A": 0,
    "_10A": 0,
    "_11A": 0,
    "_12P": 0,
    "_1P": 0,
    "_2P": 0,
    "_3P": 0,
    "_4P": 0,
    "_5P": 0,
    "_6P": 0,
    "_7P": 0,
    "_8P": 0,
    "_9P": 0,
    "_10P": 0,
    "_11P": 0,
    "_12A": 0,
    "_1A": 0,
    "_2A": 1,
    "_3A": 0,
    "_4A": 0,
    "_5A": 0,
    "_6A": 0,
    "Last 8": 0,
    "Last 24": 1,
    "Threshold": 1
  },
  {
    "Occurrence": "Packaging",
    "_7A": 10,
    "_8A": 5,
    "_9A": 8,
    "_10A": 6,
    "_11A": 4,
    "_12P": 4,
    "_1P": 3,
    "_2P": 4,
    "_3P": 4,
    "_4P": 2,
    "_5P": 7,
    "_6P": 5,
    "_7P": 4,
    "_8P": 2,
    "_9P": 3,
    "_10P": 5,
    "_11P": 1,
    "_12A": 0,
    "_1A": 6,
    "_2A": 0,
    "_3A": 1,
    "_4A": 6,
    "_5A": 6,
    "_6A": 8,
    "Last 8": 12,
    "Last 24": 104,
    "Threshold": 5
  }
]

Oooo! You're on Vision. I haven't tested with Vision. Let me play some more.

1 Like
36

So, Vision doesn't like lists and dictionaries as properties. You may have to script the creation of suitable properties using most Vision Components' MutableDynamicPropertyProvider. A bit ugly.

Consider always wrapping your expressions to yield an output dataset. (The unionAll() function is the dataset builder of this kit.)

Warning: take good backups before creating otherwise unsupported property types. If you accidentally get anything in there that Ignition's XML serializer doesn't like, you will crash.

37

Hmmm. This expression in Vision:

unionAll(
	columnsOf({Root Container.Power Table.RawData}),
	{Root Container.Power Table.RawData},
	asList(
		forEach(
			columnsOf({Root Container.Power Table.RawData}),
			case(
				it()[0],
				'Occurrence', 'Totals',
				'Threshold', 0,
				sum({Root Container.Power Table.RawData}, it()[0])
			)
		)
	)
)

Throws an error in the dataset builder.

The identical expression (except the source prop name) in Perspective:

unionAll(
	columnsOf({this.custom.rawData}),
	{this.custom.rawData},
	asList(
		forEach(
			columnsOf({this.custom.rawData}),
			case(
				it()[0],
				'Occurrence', 'Totals',
				'Threshold', 0,
				sum({this.custom.rawData}, it()[0])
			)
		)
	)
)

Yields this dataset:

"#NAMES"
"Occurrence","_7A","_8A","_9A","_10A","_11A","_12P","_1P","_2P","_3P","_4P","_5P","_6P","_7P","_8P","_9P","_10P","_11P","_12A","_1A","_2A","_3A","_4A","_5A","_6A","Last 8","Last 24","Threshold"
"#TYPES"
"str","D","D","D","D","D","D","D","D","D","D","D","D","D","D","D","D","D","D","D","D","D","D","D","D","I","D","I"
"#ROWS","5"
"No Bottles","0.0","9.0","3.0","1.0","1.0","0.0","5.0","0.0","2.0","0.0","7.0","0.0","1.0","0.0","1.0","1.0","8.0","4.0","1.0","8.0","6.0","4.0","1.0","0.0","23","63.0","6"
"No Caps","1.0","4.0","0.0","3.0","2.0","0.0","1.0","0.0","3.0","1.0","4.0","4.0","14.0","1.0","0.0","0.0","2.0","0.0","0.0","0.0","1.0","0.0","0.0","0.0","5","41.0","4"
"No Product","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","0.0","1.0","0.0","0.0","0.0","0.0","0","1.0","1"
"Packaging","10.0","5.0","8.0","6.0","4.0","4.0","3.0","4.0","4.0","2.0","7.0","5.0","4.0","2.0","3.0","5.0","1.0","0.0","6.0","0.0","1.0","6.0","6.0","8.0","12","104.0","5"
"Totals","11.0","18.0","11.0","10.0","7.0","4.0","9.0","4.0","9.0","3.0","18.0","9.0","19.0","3.0","4.0","6.0","11.0","4.0","7.0","9.0","8.0","10.0","7.0","8.0","40","209.0","0"

I will investigate further.

38

Okay, so I'm not sure if it's related to doing this in vision or not, but on upgrading to V2.0.4

This expression no longer returns a list, but rather a Dataset:

asList(
    view(
        'Select round(sum(value/60,1) \n' +
        'Where system.date.isBetween(timestamp,system.date.addHours(system.date.addHours(system.date.now(),-8),system.date.now()) \n' +
        'Group By "_".join(path.split("_")[:2]) \n',
        {Root Container.Power Table 2.historicalData}
        )
)

I replaced it with this, though perhaps there is a better way to do this, I still cant get a forEach expression to work for me.

objectScript('[args[0].getValueAt(r,0) for r in xrange(args[0].rowCount)]', /same view expression from above)

Not a huge deal, but was unexpected. I looked through this thread for where you described a change to asList() but couldn't find one. Perhaps the change was an expected result of other changes?

39

I changed asList() to unconditionally construct an ArrayList from its arguments. Before, when given a single argument, that one arg would be re-interpreted as a list. Now you get a one-element list with that argument.

1 Like
40

I have discovered this: ClientDynamicDispatchFunction

My constification routine needs to be smarter for this case.

1 Like