Row sorting question

KNIME Analytics Platform
1

Hi everyone,

I load the classification rules from a google sheet before they are put through an R snippet which formats them in the PMML style.Since I use the PMML rules dictionary node on the ‘First hit’ setting order is important .
I was hoping to know whether there was a ‘row sorter’ node that allows me to sort the rows according to two conditions.
Previously, I handled the sorting with the below SQL query:

SELECT *
FROM [dbo].[rules]
ORDER BY (CASE WHEN
[col.1] = ‘A’
OR [col.1] = ‘B’
OR [col.1] = ‘C’
THEN 1
WHEN
[col.1] = ‘D’
OR [col.1] = ‘E’
OR [col.1] = ‘F’
THEN 2
WHEN
[col.1] = ‘G’
OR [col.1] = ‘H’
OR [col.1] = ‘I’
THEN 3
END) asc, [col.date] desc

this query uses my custom defined hierarchy (the case statement) and the rows date ([col.date]) to do the sorting. It would apply the hierarchy first before the date, I was wondering whether there was a specific node I could use to accomplish this

Thanks,
Luca

2

Use Rule Engine first with
$col1$ = “A” => 1
$col1$ = “B” => 1
…
$col1$ = “I” => 3

Then use Sorter to sort.

2 Likes
3

Hi izaychik63,
Thanks for the quick response!

Using the rule engine is a good idea. by ‘Sorter’ I’m assuming you’re referring to the ‘Sorter’ node:

Capture

I don’t think this node can sort by the contents of two columns. the ORDER BY statement in SQL query orders everything by my defined hierarchy and then orders everything again, within each group, by [col.date].

is there a node that acts like ‘Sorter’ but allows you to sort in the way i described?

4

The Sorter Node allows you to sort on multiple columns but you need to choose in which order you want to sort the columns.
I think you need to sort on your hierarchy column first and next by the [col.date] and this should work the way you expect it to.

image

2 Likes
5

And if you need more than two columns in the sort, just click on the new columns button

Steve

1 Like