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.
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ā¦
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.
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!