I keep the table that get from Sql Server as a data table.
This data table has ‘expense fields’ and ‘amount’ columns. Is it possible to find the subtotal of each of these expense fields and add them as additional rows to the datatable?
if this is my data table; (i am show it for instance in excel file but in the reality it is a data table)
i want to find this; (and then i will tranfer it in a excel file)
Here is sample data folders
ExpenseField.xlsx (8.8 KB) Subtotal.xlsx (9.3 KB)
Its simple when you create a pivot table with the data you have
@mazlumkacar . Can you try to pivot out the data and check if that meets your requirement
To fine the value in the excel here is an activity called
“Find”. It will return the cell info as array.
One my friend
@VanessaCova having the situation to find and replace the text inside the excel. For that purpose I have developed some activities below.
Find - Find the text inside the sheet. It will return the cell info as a array string and row indexes.
FindReplace - Find and replace the text inside the excel sheet.
But i need to do it in data table in uipath. not in excel. How can i do it in uipath?
We can group by and sum up the value of another column
But I m currently thinking like how that can mention in subsequently grouped records as mentioned in yellow colour
group by and summing similar to
@Palaniyappan should solve the problem. In case of you need more help on this, let me know
Once we have this written to EXCEL then we can iterate again over the result datatable and
detecting each row that needs to be yellow marked on - col1 contains SUBtotal
setting the yellow markings by using the rowindex information retrievable from above
Let us know your feedback
Sorry for my explanation. The yellow color is not important. I did it because i want to everyone see that
Great, so its a little bit less to do. So just raise signal in case you need help for the group by / count.
My solution may not be the best way.
You can list the unique “Expense Field” values in the datatable. You can take a datatable list with the size of the number of elements in this list and assign the data in the main table to the relevant datatables according to the field “Expense Field”.
You can then use the Sum formula with Linq to calculate the sum of the “Amount” values. You add the total to the bottom of the table with Add Data Row.
You can merge all tables into one table with Merge Datatable. In this way, a structure is created in the format you want.
If you want to handle the table in memory, you can use LINQ, Group By and Sum for a relatively quick (almost one line) code.
You can also do it manually, ie, have an empty key-value pair like a dictionary, then loop through each row in your datatable, then:
If your (left column) value is new, add it to your list (and add the right column value)
If it’s already in the list, just add the value
But I highly suggest using linq instead.
Now i am getting this error at Group By Aggregation.
can have several options.Can you check if you are transporting empty/Null values? If yes does this error also occurs if you block for Testruns those rows
But take notice of: EXCEL shows . for thousand seperator sign
Your error shows , for this.
The non correct format errors often come from this missmatch
Yes you all right. When i pass these row i am still getting error because of NULL values.
But i dont know how will i fix it
I was thinking for some time on entire flow. Maybe another design is more suitable. I will base my ideas strict on your excel (e.g. Numberformats) and just give me some little time for my alternate idea.
If null values are crashing, then filter those values out as they dont add anything to the sums. Give a try on this
Find attached standalone demo XAML: mazlumkacar_grpby.xaml (16.8 KB)
Just do rewire the excel according your environment
for prototyping the result is written in the input Excel under new Worksheet
yellow marking the subtotals ranges is included
give a special attention on numbers and maybe change string/nmber conversions to a more appropriate conversion approach
while developing I focused on balancing maintenace options and number of activities. Thats why I dont tried to put all into single, long and less understandable statements.
Let me know your open questions and feedback.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.