Apply sql query to a table output by a node

KNIME Analytics Platform
1

Hello all,

I want to be able to treat a node output like a table and run sql against it . I understand for the most part the nodes do all the sql stuff needed but there are some instances where they don't or it's not that simple at least.

The reason I need this is that I have a count column and I want to get max(count) based on only two columns but if I do that I loose all the other columns using a GroupBy node. I'm thinking I need a nested query for it.

Thanks,

 

Amrit Grewal

2

As far as I know, there is no way to apply SQL to a KNIME table. However, have you looked at the Statistics node?

3

Hello Amrit,

one option would be to join the original table with the grouped table to add the max information to the existing table. However if you want to apply more complex sql queries you can temporary write your data table into a SQLite db, which is a file based db that does not need any installation. Once you have your data in SQLite you can query it with the database nodes. See attached workflow for an example.

Hint: The SQLite connector is part of the KNIME Connectors for Common Databases feature.

Bye,

Tobias

4

Thanks Tobias!

The SQLite Connector idea worked for me.

 

Amrit Grewal