Real time Business Logic - Excel Summation based on rows using identifier

Build
, , , ,
#1

Hello Everyone,

I would like to verify whether client records grouped based on an unique identifier tallies with bank record.

For example, in the below screenshot, records highlighted green indicate “Bank” records and can be identified using “BK” term in the string. Whereas non highlighted records are client(customer) records.

In the example below, I would like to verify whether records grouped by KID tallies with their respective bank record. For example : Records grouped by A1 would result in 7 records. out of which 6 are client records and 1 bank record. When you sum the client record values(Column3), it tallies with the Bank record(green) which is 270.

Please note that records can be in any order. For example , record with KID = “A2” might be in between two records with KID = “A1”

Can you please help me with this on how it can be done?

Thanks
Selva1
Test1.xlsx (8.9 KB)

1 Like
Excel is Empty but DataTable output is blank when queried for rows count
How to sum all values in a column of an excel/Datatable?
Shuffle rows - CSV to Excel - Using datatable
Merge Datatable - Unnecessary column displayed
#2

Hi @Selvasathappan

Seems like you need to acquaint yourself with the .Select method for rows in your data table.

Short example on KID column. Let’s assume you have read the Excel file into a DataTable variable called yourDataTable via Read Range activity.

To select all the rows with value A1 in the KID column, you can do this:
yourDataTable.Select("KID='A1'")
This returns an array of resulting Data Rows. If you want to count them, you can simply add .Count.ToString, which will print the count:
yourDataTable.Select("KID='A1'").Count.ToString -> this should print “7” in the console.

These are the basics. You can use your array in a For Each loop with an argument sent to a System.Data.Datarow and sum your values this way.
You could have an IF condition in the loop to control to control your calculations.

Please look on the forum to see plenty of examples with .Select method. You will also find out ways to sum a DataTable column in one Assign activity.

#3

Hello @loginerror

actually I don’t have a static value “A1” like shown in the screenshot. That was just an example. The values will be dynamic.I am already using select method but unable to get exact output as needed. It doesn’t work the way as expectedMain.xaml (18.3 KB)

#4

@Selvasathappan

See attachment for my solution. It is a 1-line solution that will be super hard to understand (which is super not educational) and I will provide a longer explanation later today.

But it does what you want in very few steps :slight_smile:
CheckIfSumsMatch.zip (9.1 KB)

I updated the link with cosmetic changes.

What you needed was to (in order):

  1. Get row seleciton based on a dynamic, distinct value of KID for both customers and bank ->
    For customers:
    inputDataTable.Select("KID='"+disctinctValue+"' and Column10 not like '*BK*'").CopyToDataTable()
    For bank:
    inputDataTable.Select("KID='"+disctinctValue+"' and Column10 like '*BK*'").CopyToDataTable()
  2. Summarize the values for both selections:
    .AsEnumerable.Sum(Function(x) If(IsNumeric(x(“Column3”).ToString.Trim),CDbl(x(“Column3”).ToString.Trim),0)).ToString
  3. Compare both parts to see if they match (see attached xaml)
#5

Essentially, you would need to filter your table by the “Column10” column that contain “BK”, then loop through those rows and compare the value with the array of rows that you get when you filter by the “KID” column, not including the “Column10” that contains “BK”

You can filter by using either the Filter Data Table activity (which might have some limitations) or using either .Select() which was suggested or .Where() in lambda syntax. To be honest, I think the .Where() makes more sense to me so I will use that here in my example. Lastly, you would take the filtered rows and use .Sum to add up all the values fast and compare them with the bk row that you are looping through:

For each bkRow In dt1.AsEnumerable.Where(Function(row) row("Column10").ToString.ToUpper.Contains("BK")).ToArray //use TypeArgument as DataRow
    If CDbl(bkRow("Column3")) = dt1.AsEnumerable.Where(Function(row) row("KID").ToString.Trim = bkRow("KID").ToString.Trim And Not row("Column10").ToString.ToUpper.Contains("BK")).ToArray.Sum(Function(r) CDbl(r("Column3").ToString.Trim) )
        <do actions>

so it could look something like that simply using the generic For each activity with the If activity embedded.

I hope this helps along with the other very good suggestions.

Regards.

#6

Guys, Thank you al for your responses. I had a change in logic due to the business requirement .
@ClaytonM @loginerror Can you guys please help me with the below issue?