How to find and add subtotals of values in the data table

Help
, ,
1

Hi everyone,

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?

For example;

if this is my data table; (i am show it for instance in excel file but in the reality it is a data table)

image

i want to find this; (and then i will tranfer it in a excel file)

image
image222×555 6.65 KB

Here is sample data folders
ExpenseField.xlsx (8.8 KB) Subtotal.xlsx (9.3 KB)

2 Likes
2

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

3

Hi @mazlumkacar,

To fine the value in the excel here is an activity called “Find”. It will return the cell info as array.

Regards
Balamurugan.S

4

But i need to do it in data table in uipath. not in excel. How can i do it in uipath?

@HareeshMR
@Palaniyappan

2 Likes
5

We can group by and sum up the value of another column
That’s fine
But I m currently thinking like how that can mention in subsequently grouped records as mentioned in yellow colour

image
image222×555 6.65 KB

Cheers @mazlumkacar

6

@mazlumkacar

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

7

Sorry for my explanation. The yellow color is not important. I did it because i want to everyone see that :sweat_smile:

@Palaniyappan
@ppr

1 Like
8

Great, so its a little bit less to do. So just raise signal in case you need help for the group by / count.

9

hi @mazlumkacar

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.

Good luck :slight_smile:

1 Like
10

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.

11

Now i am getting this error at Group By Aggregation.

image

image
image681×757 18.5 KB

@ppr @Palaniyappan

12

@mazlumkacar

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

13

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

@ppr

14

@mazlumkacar
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

15

@mazlumkacar
Find attached standalone demo XAML: mazlumkacar_grpby.xaml (16.8 KB)

Kindly note:

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

1 Like
16

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