How to group by data from datatable and create tables for each data

Hello everyone,

The workflow I have created gives me the total cumulative sum of each row by summing the column values ​​of each row with a while loop in a ForEachRow for the data it receives from a large table.

In this table, besides the cumulative aggregated column values, there is also an expense field and expense type columns. (an expense area contains many kinds of expenses.)

My question is:

Is it possible for me to collect the values ​​in this table by expense field and send them by mail?

For example, suppose we have 5 different expense areas and each expense area has 10 different expense types.

What should I do to set up a workflow in which 5 different e-mails will be sent with the cumulative total value of 10 different expense types in each e-mail?

I have a workflow that finds totals of column values ​​and other values. What I need is to collect these values ​​in separate tables on the basis of expense area and send mail. (5 different tables according to my example and 10 different values ​​in each table.)

I’m open to any clues or information.

You can achieve this in two ways (as per my understanding of the question) @mazlumkacar. You can filter the data table based on the type and use that data table to get the required sum

or

If you are familiar with Pivot tables in excel, you can simply do the pivot of the data which will aggregate the expense based on the row column you used.

i am not in excel. i am getting this datatable from Sql Server.

But the most importatnt thing for me is that: how will i send a table that i find by filtered table. Can i do it with : datatable.ToString or something like that?

@HareeshMR

@mazlumkacar
understanding all the details of your requirements are hard for me. Some more simple explanation along with demo data would be helpfully for solution statement creation.

On a first look a LINQ / Group By Approach should solve the scenario. To split the groups and its aggregated values onto different tables or compiling a complete report, both should technically be possible.

2 Likes

if source are datarows (e.g. LINQ statement, filter select) copytodatatable would work as long there are rows (e.g. filter result) are available

Sorry for my question. But let me try to explain my problem simply;

i have a datatable. i have to group by “expense area”. The output should give me total cumulative value for each “expense area”.

i can find total cumulative value for each row. But when i try to group by with “Group By Aggregation” activity, i cannot write which column it has to aggregate. because there is no colomn that has cumulative total.

So i am asking how can i do it with(or without) Group By Aggregation activity.

@ppr
@HareeshMR
@Palaniyappan

2 Likes

Just give us some sample data ( not too much, not to complex) So we can workout a LINQ statement. Thanks

Here is the sample data. But my real data is not in the excel. It is a datatable.

I need to have the cumulative totals for each expense area for the B1 and F1 columns separately according to the expense type.

For this sample table, there must be three separate tables named A, B, C.
And also there must be 11, 22 and 33 rows each, SumB (B1 + B2 + B3 + B4 + B5) and SumF (F1 + F2 + F3 + F4 + F5) columns.

If you can do that, you’re going to save me from what I’ve been trying to figure out for a long time:sweat_smile:

Thanks

@ppr
@HareeshMR

SampleData.xlsx (8.7 KB)

@mazlumkacar
I do feel that technically it is possible. Maybe more than 1 Activity is needed,but should not be the blocker. I will have a look on it after my work and will work out a demo xaml for you.

Just for clearification:
Cols Expense Area, Expense Type are the cols on which we do group
e.g. for group: A 11 we do Sum SumB (B1 + B2 + B3 + B4 + B5) and SumF (F1 + F2 + F3 + F4 + F5) columns.

Currently we only have 1 row for each group in the demo data right?

2 Likes

Yes, each expense area also has one expense type with the same name.

thank you so much. I will be waiting for your answer. :sweat_smile:

2 Likes

@mazlumkacar
Have a look on the Demo XAML below just showcasing some approaches.
Do also some Debuggings and Breakpoints to have a deeper look on what is received

Demo XAML: mazlumkacar.xaml (14.5 KB)

Kindly note:

  • do rewiring to your Excel
  • it seems to me that Excel format is broken as really big Numbers with the Exponential Notation were seen. But Code should be correct in Summing up
  • may some adoptions are to suggest (depends on your final detail requirements)

However just provide us a first feedback so we can close this topic soon.

2 Likes

i will make some changes on it and then send new version here. thank you so much in advance

@mazlumkacar
Sure but in General IT was working, right?

Yes it was working. But still i have to design something again :sweat_smile:

@ppr

@mazlumkacar
Thats perfect, i was Just interrested on General Feedback. So Take your time and let US know your Open questions. Once you have done please Close the topic with marking the solving Post. Thanks

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