# Sql pareto for column values per row help request **URL:** \ **Category:** Ignition\ **Created:** [May 2, 2022, 2:51pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119 "2022-05-02T14:51:11Z")\ **Posts on this page:** 20\ **Page:** 2
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [May 2, 2022, 7:47pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/21 "2022-05-02T19:47:52Z")
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](https://forum.inductiveautomation.com/t/calculating-total-time-in-seconds/49322/30) 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. ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [May 2, 2022, 8:05pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/22 "2022-05-02T20:05:52Z")
> [@zacharyw.larson](#): > > Where’d the “u” comes from in the output? That just says it's unicode. if you print the actual element of the list the 'u' won't show. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [May 2, 2022, 8:25pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/23 "2022-05-02T20:25:22Z")
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. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [May 3, 2022, 7:43pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/24 "2022-05-03T19:43:49Z")
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. ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [May 3, 2022, 7:51pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/25 "2022-05-03T19:51:56Z")
> [@zacharyw.larson](#): > > no script scheduler. I'm thinking the company is hamstringing itself if it's not using the tools available to it. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [May 3, 2022, 8:10pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/26 "2022-05-03T20:10:31Z")
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: ~~ ```auto 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 ```auto 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 ```auto 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** ```auto 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. ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [May 3, 2022, 8:31pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/27 "2022-05-03T20:31:05Z")
What I gave was a proof of concept. What do you envision for your table layout? ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [May 3, 2022, 8:34pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/28 "2022-05-03T20:34:40Z")
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 ```auto (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 ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [May 4, 2022, 7:47am UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/29 "2022-05-04T07:47:34Z")
I'm not qualified to help with sql, but... > [@zacharyw.larson](#): > > `pareto = [[x, y] for y, x in reversed(sorted(zip(data, filteredHeaders)))][:5]` `sorted` can take a `reverse` parameter, so you could do this instead: ```python 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. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [May 4, 2022, 1:45pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/30 "2022-05-04T13:45:02Z")
ah we use reverse for descending I got this when I tried to print ```auto print dataIN ``` or I get each value on a new line with this ```auto 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 ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [May 4, 2022, 2:00pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/31 "2022-05-04T14:00:38Z")
> [@zacharyw.larson](#): > > ```auto > 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) > ? The advantage of a pyDataset is you don't have to go to all of that trouble. ```auto for row in dataIN: for value in row: print value ``` ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [May 4, 2022, 2:08pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/32 "2022-05-04T14:08:43Z")
I really hope they make basic datasets iterable someday ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [May 4, 2022, 2:32pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/33 "2022-05-04T14:32:55Z")
> [@zacharyw.larson](#): > > I got this when I tried to print > > ```python > print dataIN > > > > ``` Here's a function to help with that. > [@View Dataset from script console](https://forum.inductiveautomation.com/t/view-dataset-from-script-console/36704/4): > > Sorry, I'm on vacation, but family is napping, so I get to play. Yes, this is how I relax. Don't judge. EDIT: Added option for printing from Perspective. Sorry, not quite ready for prime time. roll_eyes def printDataSet(datasetIn): from itertools import izip import ast if 'PropertyTreeScriptWrapper$ArrayWrapper' in str(type(datasetIn)): headers = datasetIn[0].keys() data = [] for row in datasetIn: data.append([item.value for item in row.values()]) datasetIn = system.dataset… ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [May 4, 2022, 2:35pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/34 "2022-05-04T14:35:00Z")
error: line 14 list object has no attribute getRowCount lol nooooooo I defined namedQuery, reasonHeaders, params, and dataIn ```auto 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) ``` ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [May 4, 2022, 2:37pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/35 "2022-05-04T14:37:09Z")
Well, your header is not a dataset, you can’t call dataset methods on it ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [May 4, 2022, 2:40pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/36 "2022-05-04T14:40:31Z")
And, you only need to get the filtered headers once. ```python 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] ``` ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [May 4, 2022, 2:45pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/37 "2022-05-04T14:45:16Z")
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 ```auto print filteredHeaders ``` ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [May 4, 2022, 3:34pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/38 "2022-05-04T15:34:14Z")
```auto 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 ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [May 4, 2022, 6:05pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/39 "2022-05-04T18:05:27Z")
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’] ```auto 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 ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [May 4, 2022, 6:31pm UTC](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119/40 "2022-05-04T18:31:55Z")
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”? [Previous page](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119.md?page=1) [Next page](https://forum.inductiveautomation.com/t/sql-pareto-for-column-values-per-row-help-request/59119.md?page=3)