How to calculate total of columns for dynamically added columns in a data table?

Hi guys, I’m working on a small project which has a file structure like this:

image

Based on the headers in the result file, I have to check if the column exists in the master file and if it does, for each category, I have to calculate the total and write it to the result file.

What is the best way to do this?

I tried to use a Build Data Table activity to create the output structure (dynamically using loops) and then tried to use an Add Data Row activity but, I’m not getting the proper output.

Please help.

1 Like

@monsieurrahul
Are you expecting that for column X in master

  • the column will just ommited for the sum building
  • or bot should throw an exception and will do no sum building for any column

That will keep varying each time.

Consider the result file screenshot above. The highlighted column F is a newly added column which might exist in the master file and if it does, we have to calculate the total for each distinct categories and if it doesn’t exist, we simply mark it as 0.

I’ve done everything but, I’m not sure how to use an Add Data Row activity dynamically.

@monsieurrahul
My question was in result we do see E, but not in Master (as we do see X). What is the expected behaviour? Please tell us the expectations.

Good to know from your feedback was as E is missing in Master we will write 0.

I’ll tell you the steps the bot is going to perform:

  1. Read the result file and form a table with headers in it.
  2. For each distinct category in master file, filter the data and perform the following:
    2.1 Check if the columns in the result table exists in the filtered table.
    2.2 If it exists, calculate the total and if not, update as 0 (Since E doesn’t exist in master).
    2.3 Update the row to the result DT since it has headers.
    2.4 Repeat until all the categories have been completed.

Where I’m stuck: Step 2.3. I don’t know how to use Add Data Row since we can’t predict the order in which columns exist.

Anyone else who could guide me on this?

@monsieurrahul
with dtResult.NewRow (to var tmpRow you will get an empty row for result
With dtResult.Columns you can iterate over the columns and can get the name with the ColumName Property

within the iteration you can check if Column is dtMaster (and also ommit the Category Column as there is nothing to sum up).
Summing up can be done with datasetextension Method Sum

and the result can be assigned to tmpRow(YourCurrentColumnName)

after all you add tmpRow to dtResult.

In case of you need more help, just share a xaml with in which have prepared two datatable with sample data. I could help you on finalizing with this

1 Like

@ppr I’m getting an error in adding the rows and I can’t share the workflow since it is official so, can you please consider sharing a .xaml file for the problem?

Here’s a sample Excel file for your reference: Test.xlsx (8.9 KB)

Thanks a ton!

@monsieurrahul

Category A B C P
SUM 280 280 280 0

expected output is this, right? I will have a look on it after my jobwork

@ppr Close. It’s more like this:

image

And if the column doesn’t exist in the result file, we’ve to update it as 0 (got that right in your response for column named ‘P’).

Thanks man!

@monsieurrahul
Find starter help here:
monsieurrahul.xaml (15.3 KB)

when you debug and pause on last comment the result of dtResult will be:

[Category,A,B,C,P
ABC,130,190,130,0
XYZ,30,50,30,0
PQR,120,40,120,0
]

Dont forget to rewire the path to Excel as by your end filepath

1 Like

@ppr Thanks for sharing this!

I actually solved the problem on my own. I used an invoke code activity and wrote C# code.

I used a column counter to iterate through every column, used the column name property to get the column name and then used a if condition to check if it exists in my master data table. If it does, I’ll add it up and used the extension method to pass it to the result data table. I also used a row counter to add data to the same data table and not generate a new one, every time.

FYR: https://stackoverflow.com/questions/19629644/how-to-edit-a-row-in-the-datatable

Thanks again for your guidance and help @ppr! I really appreciate it! :slight_smile:

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