Sort Dataset Based on Two Column

Ignition
, , ,
1

Hello everyone,

I’m trying to sort a dataset based on two columns and tried following another post linked here to get the desired outcome, but I wasn’t successful. I was wondering if anyone has any insights/recommendations?

For reference, I wrote the code below as part of my if-else statement. It sorts the table based on one column but doesn’t work when I add the second column:

activeOrderDataset = system.dataset.sort(activeOrderDataset,'Col1')
activeOrderDataset = system.dataset.sort(activeOrderDataset,'Col2')

pyActiveOrderDataSet = system.dataset.toPyDataSet(activeOrderDataset)
2

Do you need to keep it in dataset format, or would python types (a list of objects) be acceptable ?

edit: This will keep it in dataset format anyway:

return system.dataset.toDataSet(
	list(value.columnNames),
	sorted(
		system.dataset.toPyDataSet(value),
		key=lambda row: (row[col_1], row[col_2])
	)
)

Note that col_1 and col_2 can be either column names or indices

2 Likes
3

If this is for a binding, you might find my orderBy() expression function most convenient.

4

Is this supported in Ignition 8.1?
I will give it a try.

5

Do you have an example of how it can be used?
or example of its key expression?

6

Sure:

orderBy(
	{path.to.activeOrderDataset},
	it()['Col1'],
	it()['Col2']
)

As an expression binding on the property where you wish the ordered dataset to be placed.

3 Likes
7

Thank you!!! Any kind of import to happen before using OrderBy()?
Do I need to install a module?
Assuming we have to import the command? Cause I just cannot find any example or description for it.

8

here: Ignition Third-Party Module Showcase

1 Like
11

Thank you! I have imported this and did gateway reset. It's just I don't know how to get it into Python.

12

You don't, its an expression. Use an expression binding

1 Like
13

It isn't a python function, but an expression.

1 Like
14

I have tried this and it worked. The only issue is that it works only if you have a table with one page once the data gets bigger and the table goes into the 2nd page it won't work for the 2nd page and the pages afterward. Thank you!!

15

Your solution worked better cause when you have a table with multiple pages, the expression binding does not work.

So I used your version, those two columns were the last two columns of my table so I sorted based on the second last and then the last:

sortedData = sorted(
activeOrderData,
key=lambda row: (row[-2],row[-1])
)

16

No, you just have to use a binding on a custom property to do the sorting. Then provide that to the table dataset, that way you can do the sorting prior to it being "paged".

3 Likes
17

I did expression binding on Data Property of the power table and it did not work, but maybe yes I could use your method to do binding on custom property.

18

Is there any reason why it needs to be sorted in front end and not the database itself?

19

She never said a database was involved.

Let's make things clearer:
If your data comes from a query and you can sort it there, then sure, it's the way to go.

But if you're asking there how to sort a dataset, I assume it's because you can't do it in your queries.

Also note that the solution I suggested is NOT on the front end. It will not be executed by the browser, but by the gateway.

20

In post below it is mentioned that the dataset is the result of a SQL query

Is there a way to sort a dataset on two different columns. The ****data in the dataset is the result of **
a SQL query. Lets say i have a dateset with 10 column**s. I have a column that has a flag that is set for example either UP or Down. Another column has a Sequence number that is unique for the two different types. So in the data I have 10 UPs and 10 Downs for a total of 20 rows. I want to sort first on the Type and get all my UPs, then within each type I want to sort by the sequence number so i end

21

But this post you mention is not from the same user.

22

That is the post she linked and not her post. Just because she linked to a post which references a SQL query doesn’t mean that she is getting her data from a SQL query.

To answer your question directly, if the user has the ability to change the sort order then for relatively small data sets it will be more performant to do the sort in the front end. As the dataset grows in size that will shift to the back end.

There is really no reason here to suggest one way or the other, and since the OP was already doing the sort on the front end the suggested solution was also front end based.

1 Like