Create Expression to filter Rows and Columns of SQL Query

Ignition
1

Hey, I have a simple question regarding transforming a query. I have this SQL query shown below with 4 columns; tag, status, datetime, and minutes.
image

I want to run an expression on it so I can pick out a specific cell based on the tag and status. I know I can pick out a specific cell with {value}[0,1], but I’d like to search for minutes IF tag=“position 10 status” AND status=“Running”
image

Thank you for the help!

2

There’s a module called SimulationAids that you can use to filter datasets with “pseudo-sql”. I recently started using it and its very easy. I don’t think there’s any built in way to do it via expressions. You’d either need to add WHERE clause parameters to your query that you modify, use python/jython to filter though that can get messy and would I would guess would be slower than the module, or you can use the free module.

2 Likes
3

Awesome! I’ll try this out!

1 Like
4

This script transform might work for you. Try it in the script console.

d = [
  {"tag": "position 10 status", "status": "paused", "minutes": 2.27},
  {"tag": "position 10 status", "status": "Running", "minutes": 121.59},
  {"tag": "position 100 status", "status": "paused", "minutes": 0.06},
  {"tag": "position 100 status", "status": "Running", "minutes": 0.98},
  {"tag": "position 120 status", "status": "paused", "minutes": 122.93},
  {"tag": "position 120 status", "status": "Running", "minutes": 0.93},
  {"tag": "position 130 status", "status": "paused", "minutes": 1.23},
  {"tag": "position 130 status", "status": "Running", "minutes": 2.34},
  {"tag": "position 140 status", "status": "paused", "minutes": 3.45},
  {"tag": "position 140 status", "status": "Running", "minutes": 4.56}
]

filter(lambda row: 	row['tag'] == 'position 10 status' and row['status'] == 'Running', 	d)[0]['minutes']

For your binding transform you would use something like this:

# value is returned by the binding.
return filter(lambda row: 	row['tag'] == 'position 10 status' 
                     and row['status'] == 'Running', value)[0]['minutes']

I’m no Python guru. I got the technique on Python list of dictionaries search - Stack Overflow and there are a few other suggestions. Remember that Ignition is using Jython 2.7 so restrict your research to Python 2.7 solutions.

1 Like
5

I was able to resolve this by making parameters in my Named Query. By making parameters I was able to filter the query by sensor and status to get a single cell.
image

2 Likes