How do I get a row count of a data set at a specific value?

Ignition
, , ,
1

I don’t have the newest version of Ignition so I don’t have the ability to pass values from my filtered tables dataset to a dataset tag. I have a filtered table and would like to get the counts of different values in a specific column into some memory tag. So that I can pass them over to a chart.

Ex: Column Name “Repair Type”. Possible Values: Broken Pipe, Snapped Wire, Bend Frame… etc

If someone could help me figure this out it would be a huge help for me. I would use sql tags and get the counts that way but I need the count to match the information in the filtered table dataset so I can pass them to a chart in another window. The chart will be based off the filtered table.

2

Need just a little more information:

  • What version of Igniton are you using?

  • Filtered data means what? Is this in a power table?

3
  • Currently running Ignition version 7.6.7.
  • By filtered data I mean I have a table, non power table, and I have added drop downs with a custom “where” properties that add to the end of my select statement for my table dataset that allow me to get filtered results on my table.

In short I basically want to use the filtered results of this dataset to pass values to a chart that is in a separate window. Specifically I have a column that has different possible values. I want to take a count of each of the four values in this column and display them in a chart from greatest to least count.

I am struggling because I do not have a array/dataset tag and when pulling my dataset to alter with python I haven’t found a way to only pull values from a column with a specific value and then do a rowcount of how many were returned to then pass to a memory tag.

4

Upside:

  • You can use a Client tag as a dataset type, so you do have that going for you. As an alternative, If you open the second window (with the chart) from here, you can pass the dataset as a parameter. No tags to worry about.

  • The filtering is done, so you’re most of the way there.

  • As a bonus, I think that sorting datasets came in around 7.4, so all we have left is to count the items

Downside:

  • I think you’re still under Jython 2.1. This gives a bit more in hoops to jump through, but not impossible.

Setting this as a propertyChange event in the table:

if event.propertyName == 'data':

  def pareto(listIn):
    import system
    # Initialize headers and data for the output dataset
    headers = ['item', 'itemCount']
    dataOut = []
    
    # Initialize dictionary
    counts = {}
    
    for element in listIn:
      # check to see if it already exists
      if counts.has_key(element):
        counts[element] += 1
      else:
        counts[element] = 1 
    # get dictionary keys and values     
    k = counts.keys()
    v = counts.values()
    
    # iterate through to make list for output dataset
    for i in range(len(k)):
      dataOut.append([k[i], v[i]])
    # create dataset
    datasetOut = system.dataset.toDataSet(headers, dataOut)
    # sort the dataset and return
    return system.dataset.sort(datasetOut, 'itemCount', False)
  
  # get dataset from the table
  dataIn = system.dataset.toPyDataSet(event.source.data)

  #initialize a list for the items to count
  columnData = []
  
  for row in dataIn:
    # write column value of interest to the list
    columnData.append(row['Col 2'])
    
  #write to client tag
  system.tag.write('[Client]Sorted Data', pareto(columnData))
5

Try using either GROUP BY or PIVOT in the pseudo-SQL of the view() expression function from the Simulation Aids module. (-:

6

I managed to get the counts of my data into memory tags. Now I am trying to get those memory tags into a dataset to use in a chart and basically make a pareto out of the dataset.

I currently have this on the property change event for the chart.

___________________________________________________________________

bloCnt = system.tag.read(“TouchupCounts/blowthroughCountVal”).value
lenCnt = system.tag.read(“TouchupCounts/lengthCountVal”).value
ntuCnt = system.tag.read(“TouchupCounts/notouchupCountVal”).value
offCnt = system.tag.read(“TouchupCounts/offlocationCountVal”).value
othCnt = system.tag.read(“TouchupCounts/otherCountVal”).value
porCnt = system.tag.read(“TouchupCounts/porosityCountVal”).value

headers = [“Blow Through”, “Length”, “No Touch Up”, “Off Location”, “Other”, “Porosity”]

data = []

data.append([bloCnt,lenCnt,ntuCnt,offCnt,othCnt,porCnt])

countDs = system.dataset.toDataSet(headers,data)

/* commented out currently
pds = system.dataset.toPyDataSet(countDs)

for row in pds:
_ for value in row:_
_ print value_
_ _
event.source.pythonDataset = system.dataset.toDataSet(pds)
*/

for row in range(countDs.rowCount):
for col in range(countDs.columnCount):
print data.getValueAt(row, col)

event.source.pythonDataset = system.dataset.toDataSet(countDs)

___________________________________________________________________

I would like to have it clear the dataset values each time the tag values change as well. I’m kind of confused on how to call this into the dataset on the chart though.

You have been a great help so far I’m just really new to ignition myself.

7

Did you try using a Client tag? You should have been able to directly store the sorted dataset to one. This seems like a lot of extra work for data that should already have been generated from the above script (modified to fit your table, of course).

Here is a v7.6-friendly project that should illustrate what I’m talking about. Every press of the ‘New Data’ button refreshes the test data in the left-hand table. a propertyChange script on the left-hand table counts and sorts the elements of Col 2, creating a new dataset (dataOut-- I don’t get too crazy with names… ;)). dataOut gets written to the right-hand table, and to a Client tag called ‘Sorted Data’

The two charts display the results. on points to the right-hand table data, the other to the Client tag.

Hope this point you in the right direction.

JC_Pareto_v7.6.7_2018-08-21_1239.proj (22.5 KB)

2018-08-21_12-43-01.jpg1173×964 177 KB