I connected to postegreSQL and I did two queries, then I used DB reader in order to write the output on CSV, as the example:
But, how can i write on the same CSV both queries (in two different sheets)?
I would like to have only one csv with all queries extracted
CSV files do not have any sheets. You could try and use excel or a small database to store your results in one file.
assuming you would like to create an Excel file with two different sheets for both results.
You should create your Excel file first by using “Excel Writer (xls)” node. There you configure the name of the file and the name of the first sheet. After that you can create a second sheet in the file by using the Excel sheet appender (xls) node. Make sure, that you first create the file and then append the second sheet (can be forced by a flow connection) - of course the file path should be identical.
Hope this helps, Tommy
I have another question after this: is it possible to create in the excel obtained from excel sheet appender, one column calculated? for example, in my excel writer I have two columns derived from queries etc. once I arrived to excel sheet appender, can I add a column into the excel and make sum, count etc? as in a normal excel but using Knime…
I do not know if I have to do this before excel writer or after…
Thanks in advance
Hi @giad -
You would need to do these types of aggregations prior to writing to an Excel file. Maybe the GroupBy or Math Formula nodes would help?
I have done a query in which I have to count a field…I obtained a column “count”, now I have to put a condition on this column because I have to sum these count based on data, but I cannot do this with a db query, as previously.
it is only a sum on a column but I do not know how to calculate it.
Can you give a short example of your data, and the query you were previously using to calculate the sum?
So, I connected to postgresql and then made a query on a table:
SELECT date_trunc(‘month’, date) AS month, count (distinct id)
FROM #table# AS “users”
group by month
order by month desc
so, I have now two columns: month and count.
now I need to create in a third column the cumulative sum like this:
Is it possible to make this sum in a query or another node in Knime?
Then a db reader and at the end an excel writer in order to have the output of three columns.
Ah, OK. This looks like a job for the Moving Aggregation node with the Cumulative computation box enabled. Use an aggregation of Sum on whatever you’re trying to add up. Here’s a simple example on a column with a single variable:
great it works. But I need the sum based on the month descending, so the total sum starting from the most recent month up the last month (less recent). So the sum will be descending.
how can I do that?
You can use a Sorter node prior to the sum operation to get things in the order you need.
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.