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:
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.
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.
Hope this helps, Tommy
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?
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:
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.
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.
