Aggregate Data in DataTable

Help Studio
, ,
1

Hi Everyone,

I have a scenario where I need to check a master excel sheet.

image
image1714×382 32 KB

and compare with another output excel sheet.

image
image1766×401 165 KB

In both the sheets my comparison column name is INTERNAL ID
If match found for example multiple in this case 2804 and 2805 is found in both the sheets or

image
image1887×303 113 KB
then I should take the aggregate of N Column included units 1700+1700 else sometimes

sometimes only one internal id which is present may be matching then I should take just 1700 as included units.

image
image1814×238 94.1 KB

I have done the following steps:
reading the outputsheet and storing in datatable-ExtractedData
reading the master data sheet and storing in data table -dt_sku
Taking a for each row in datatable (ExtractedData) and under that again
for each row datatable -dt_sku
Putting if condition
CurrentRow.Item(“INTERNAL ID”).ToString=Row.Item(“INTERNAL ID”).ToString And CurrentRow.Item(“RATE TYPE”).ToString=“Prepaid Subscription”

Then ,
Add DataRow,
CurrentRow.Item(“INTERNAL ID”).ToString=Row.Item(“INTERNAL ID”).ToString And CurrentRow.Item(“RATE TYPE”).ToString=“Prepaid Subscription”

image

image

ExtractedData.AsEnumerable.Where(function(x) x(“INTERNAL ID”).ToString.Equals(Row.Item(“INTERNAL ID”).ToString) ).Sum(function(x) CDBL(x(“Included units”).ToString))
However In my case I am getting 0 value even though Included units value in main sheet is 1700 and master sheet INTERNAL ID and Main sheet INTERNAL ID is matching

2

@marina.dutta

Try this

requiredsum = dt.AsEnumerable.Where(function(x) dt2.AsEnumerable.Select(function(y) y("Internal_ID").ToString.Trim).ToArray.Contains(x("Internal_ID").ToString.Trim)).Sum(function(x) CDBL(x("Include Units").ToString))

no loop is needed dt is the table where you have all columsn and dt2 is the table where you have only internal_ID

cheers

3

try to use LINQ queries no looping is required

4

@Anil_G

If I have three different conditions to check and then take the sum of Included Units. How shall we do that? where CurrentRow is for data table dt. I am checking in one single data table all the conditions.

Condition 1. CurrentRow.Item(“RATE TYPE”).ToString="Prepaid Subscription"and Convert.ToDateTime(CurrentRow(“START DATE”).Tostring) =StrStartDate And not CurrentRow.Item(“NAME”).ToString.Contains(“Rollover”)

Condition 2.CurrentRow.Item(“RATE TYPE”).ToString="Prepaid Subscription"and Convert.ToDateTime(CurrentRow(“START DATE”).Tostring) >StrStartDate And not CurrentRow.Item(“NAME”).ToString.Contains(“Rollover”)

Condition 3.CurrentRow(“NAME”).ToString.Contains(“Rollover”)Or CurrentRow(“NAME”).ToString.Contains(“rollover”)Or CurrentRow(“NAME”).ToString.Contains(“ROLLOVER”) And CurrentRow.Item(“RATE TYPE”).ToString=“Prepaid Subscription”

5

@marina.dutta

just include any condition you need inside where

example

requiredsum = dt.AsEnumerable.Where(function(x) x("RATE TYPE").ToString="Prepaid Subscription"and Convert.ToDateTime(x("START DATE").Tostring) =StrStartDate And not x.Item("NAME").ToString.Contains("Rollover") AndAlso dt2.AsEnumerable.Select(function(y) y("Internal_ID").ToString.Trim).ToArray.Contains(x("Internal_ID").ToString.Trim)).Sum(function(x) CDBL(x("Include Units").ToString))

similarly follow for others

cheers

6

@Anil_G

Thank You. It works

1 Like
7

@Anil_G

one question. Sometimes the “INCLUDED UNITS” may be null or empty .In that case the condition should not throw error.

When I was using For Loop , I used the conditions

image
image1730×1352 171 KB

But in using Linq , how to include the condition for Null or Empty

8

@marina.dutta

try this

requiredsum = dt.AsEnumerable.Where(function(x) x("RATE TYPE").ToString="Prepaid Subscription"and Convert.ToDateTime(x("START DATE").Tostring) =StrStartDate And not x.Item("NAME").ToString.Contains("Rollover") AndAlso dt2.AsEnumerable.Select(function(y) y("Internal_ID").ToString.Trim).ToArray.Contains(x("Internal_ID").ToString.Trim)).Sum(function(x) If(IsNothing(x("Include Units")) OrElse String.IsNullOrEmpty(x("Include Units").ToString),0,CDBL(x("Include Units").ToString)))

cheers

9

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