Write the output of two queries on the same CSV, but in two different sheets

Hi everyone,
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:
image

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

Thanks!
Giad

CSV files do not have any sheets. You could try and use excel or a small database to store your results in one file.

3 Likes

Hi @Giad,

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.
image

Hope this helps, Tommy

4 Likes

Great thanks!
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
giada

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?

1 Like

Hi @ScottF
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.

Many thanks
Giada

Can you give a short example of your data, and the query you were previously using to calculate the sum?

of course.
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:

month count cumulative sum
01/02/2020 00:00 1821 15216
01/01/2020 00:00 1490 13395
01/12/2019 00:00 1287 11905
01/11/2019 00:00 980 10618
01/10/2019 00:00 1610 9638
01/09/2019 00:00 1523 8028
01/08/2019 00:00 1754 6505
01/07/2019 00:00 1589 4751
01/06/2019 00:00 1439 3162
01/05/2019 00:00 1000 1723
01/04/2019 00:00 450 723
01/03/2019 00:00 234 273
01/02/2019 00:00 34 39
01/01/2019 00:00 5 5

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.
Many thanks
Giada

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:

2020-03-17 14_40_21-Window

2 Likes

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?
thanks!

You can use a Sorter node prior to the sum operation to get things in the order you need.

1 Like

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.