Sql pareto for column values per row help request

Ignition
21

Where’d the “u” comes from in the output?

You made that scripting method easy to understand. Thanks @JordanCClark

Company uses 8.1.0 Ignition, no script scheduler. So I tend to use prepared queries.

Was able to finish a pareto chart this afternoon by making a fault table, transaction groups, and a query like the one Phil and a few of you helped me with last year:

HERE

Not the same as the wide table.

Wanted to finish today, and didn’t know how else.

Thanks for helping me.
I checked the links. That example with the unpivot didn’t make sense to me.
Then when I clicked the link recommended off from it with the phone types, that was confusing enough that I switched gears to make the tall table and query.

1 Like
22

That just says it's unicode. if you print the actual element of the list the 'u' won't show.

1 Like
23

Ah, I think I have to make a wide table work for scrap tomorrow.

Looks like one of the techniques is making a subquery that converts the wide table to a tall table by combining the wide columns into one column. I think it was confusing to me because I didn’t know how I would get the t_stamp to work with it, but now I have an idea.
I think I can do that, and concat some other columns as needed.

1 Like
24

These stack exchange posts seem to stretch back 10 years and still don’t have good solutions.

I don’t know what the solution is in sql, but it is not trivial for sure.

My idea with concat did not work.

25

I'm thinking the company is hamstringing itself if it's not using the tools available to it.

1 Like
26

I am sure they are trading reliability of third party dashboards for all the features and big fixes.
I don’t like to mention it, but it seems like each post I must mention once or some ideas that work on more updated versions come up.

The script way works in the console for the testing.

To make it work for my application though:

I need to

  1. make table on a perspective page
  2. bind the data:
data =[]
dataIN=system.dataset.toPyDataSet(system.db.runNamedQuery(namedQuery, parameters))

or since it is in a bindings

  1. query binding to my named query filtered for machine and time range

  2. then use the script transform

  3. in the script transform I use

data =[]
dataIN=system.dataset.toPyDataSet(value)
  1. include the header filtering
  2. perform the reversed, sorted, zip which gets data into rows with columns sorted by the max
return pareto

Pareto in the script is a pydataset?
pareto variable became a pydataset because filteredheaders became a pydataset in the list comprehension getting the column names rom dataIn

a pydataset can be returned as the data for a table in perspective if I understand correctly

I plan to try this in the morning. I don’t think I can break it, but my brain is jello.
Tried for a long time to find a way that a wide table can be converted to tall table

functionally

for row in dataIn:
	data = [row[colName] for colName in filteredHeaders]
	
	#Sorting a zip sorts all lists by the first one.
	pareto = [[x, y] for y, x in reversed(sorted(zip(data, filteredHeaders)))][:5]

	print row['t_stamp'], pareto

dataIn is a pydataset because set it as one

filteredheaders is a pydataset because it is comprised of dataIn
data is a pydataset because it is comprised of filteredheaders
pareto is a pydataset

so I am not sure I understand
I have 5 pages of the manual open, but am not figuring it out.
I think a few examples are just shy of filling in the gaps for me.
I understand SQL much better, but this seems like something that can’t be done in SQL.
Or if it can be, it requires a query of the information schema to get the column names and manipulate them, which I am pretty sure I can’t do because of permissions.

1 Like
27

What I gave was a proof of concept. What do you envision for your table layout?

1 Like
28

I thought I would get the proof of concept to work in a table on a view.
Then I would use what I had learned to make the actual thing I would display.

My vision is a table of scrap reasons per line sorted by the amount of scrap


(line ) ( scrap reason) (# scrapped) (percent of total scrapped) ( total scrapped)

[ rows of  reasons for scrap that are sorted by the amount scrapped]

I did this for my faults, but I did it with a query.
I had the transaction group setup perfect for it though. One column was the code and the transaction triggered when that code changed.

I am not a fan of working with counters in a plc to setup a table in Ignition.
I prefer to get increments from the plc, make transaction groups capture the increments, then query those to make my counts. After this though, maybe counters will be easier.

wish I got it to work in a query, I want to do some graphs with the data too

29

I'm not qualified to help with sql, but...

sorted can take a reverse parameter, so you could do this instead:

pareto = [[x, y] for y, x in sorted(zip(data, filteredHeaders), reverse=True)][:5]

Not having several parentheses next to each other makes me happier.

2 Likes
30

ah

we use reverse for descending

I got this when I tried to print

print dataIN

<PyDataset rows:1 cols:30>

or I get each value on a new line with this

for row in range(dataIN.getRowCount()):
	for col in range(dataIN.getColumnCount()):
		print dataIN.getValueAt(row,col)

trying to get the header next to it, print someheaderthing+dataIN.getValueAt(row,col)
?

oh I see, you zipped the header with the data to get them in the same row,col

31

The advantage of a pyDataset is you don't have to go to all of that trouble.

for row in dataIN:
    for value in row:
        print value
2 Likes
32

I really hope they make basic datasets iterable someday

3 Likes
33

Here's a function to help with that.

2 Likes
34

error: line 14
list object has no attribute getRowCount
lol nooooooo

I defined namedQuery, reasonHeaders, params, and dataIn


6   dataIn=system.dataset.toPyDataSet(system.db.runNamedQuery(namedQuery,params))

7   print datatIn

11  for row in dataIn:
12        filtered headers = [colName for colName in dataIn.getColumnName() if colName in reasonHeaders]

14     for row in range(filteredHeaders.getRowCount()):
15	     for col in range(filteredHeaders.getColumnCount()):
16		   print filteredHeaders.getValueAt(row,col)
35

Well, your header is not a dataset, you can’t call dataset methods on it

2 Likes
36

And, you only need to get the filtered headers once.

dataIn=system.dataset.toPyDataSet(system.db.runNamedQuery(namedQuery,params))

print datatIn
filteredHeaders = [colName for colName in dataIn.getColumnNames() if colName in reasonHeaders]

for row in dataIn:
	for col in filteredHeaders:
		print col, ' : ', row[col]
1 Like
37

so the function you posted to help, that lets you view a dataset from script console

I thought I already could do that with the two ways in LRose’s post?
OHH your post adds the structure, so you can visualize it, got it

oh so I just print the list to check it

print filteredHeaders
1 Like
38 
dataIn=system.dataset.toPyDataSet(value)
	
reasonHeaders = ['thisReason', 'thatReason', 'nReason']


for row in dataIn:
	data=[row[colName] for colName in reasonHeaders]	
	pareto = [[x, y] for y, x in reversed(sorted(zip(data, reasonHeaders)))]

return pareto

omg it is a alive,

on the table, I set field to column_1 and the next one to colomn_2
then I set the headers to reason and quantity

Now I just have to go back and edit the dates into query and some summing

seems to be handling none type well so far, but if I add summing, I probably need isnull

I have it looking great, except when the values are 0 for reason, they are listed in reverse alphabetical order haha

nobody has complained yet

39

I have to add net and defects to the table, then also line and a percentage of defects per net

However, I am not getting an error, yet it is not working.

time is going too long on the script execute, says interrupt on the button

I defined reasonHeaders and suffixHeaders.
reasonHeaders=[‘thisReason’,…‘nReason’]
suffixHeaders=[‘thisNet’,…‘nCalculation’]

for row in dataIn:
	data=[row[colName] for colName in reasonHeaders]	
	pareto = [[x, y] for y, x in reversed(sorted(zip(data, reasonHeaders)))]

#print pareto



for row in dataIn:
	data2=[row[colName] for colName in suffixHeaders]	
	testThis =[[x,y] for y, x in zip(data2,suffixHeaders)]
	
print testThis


for row in pareto:
	for col in pareto:
		pareto.append(testThis)
	
print '-----'
	
print pareto

I thought if I got them both in lists, I could do something like:
for column append

keeps timing out, I am reviewing append function

40

I am not sure how the append function handles appending a list of lists to another lists of lists, but I’m not certain that’s really what you’re looking for anyway.

Also, I don’t believe there will be any data in testThis, unless there are actually column names in dataIN that actually match the suffixHeaders.

How are you defining “net”, “defects”, “line”, and “percentage of defects per net”?

1 Like