# Need help in getting calculated values in a data table column without looping and grouping based on another column

Below screenshot will give more details

Assume I have a data table with the first 3 columns and added 4th column. The problem is I need to get the sum of all the Credit amounts grouped by the CIF value in all the rows and update the same in the 4th column. Loop is not an option as the number of records expected is more than 2 lacks

My expectation is to avoid loops using LinQ query or any other feasible solution which is simple and powerful. Please help me in solving the same

Please note that the output data table should have the same number of input rows

@Palaniyappan ?? Any suggestion you have as i can see your replies related to LINQ queries in many posts

Hi,

How about using Dictinoary as the following sample?

Sample20220126-5.zip (3.0 KB)

Regards,

@sharazkm33

Thanks

But the output rows are different from input rows. The output shouldn’t be grouped. Output should be matching with input dt with same number of rows

Checking your solution. But i can see a complete loop through the data table which needs to be eliminated as the number of rows expected are more than 2 lacks and this might affect the performance of BOT

have a look here for some more trainings

with the reuse of the calculated sums the dictionary approach from @Yoichi is a recommendable approach for this case

Hi,

Alright. I rewrote the loop to LINQ. Can you try the following?

Sample20220126-5v2.zip (2.9 KB)

Regards,

1 Like

Thanks bunch bro…worked as expected … Impressed by your solution…kudos

1 Like

@Yoichi

I have integrated my code with your help. But for some of the columns - calculation is not sum of any column. Instead we ned to do difference / multiplication / division etc… Is it something we can accommodate by modifying your expression

Example

Age Column = Current year - Year in Birth Column
DBR = Column A / Column B
XYZ = (Column C/ ColumnD)*.5

Hi,

Can you give us specific samples : input and expected output?

Regards,

Sure \

We did solution for Coluimn G where grouping is required. I am struggling to write similar query for column H and I as i am not much familiar with Linq queries. Other columns does not require grouping and why i am specifically going with queries is to avoid loop as the number will be in lacks

@Yoichi Please let me know if you need more details

Hi,

``````dt =dt.AsEnumerable.Select(Function(r) dt.Clone.LoadDataRow(r.ItemArray.Take(6).Concat({dict(r("CIF Number").ToString),(Double.Parse(r("CIF_Balance").ToString)/Double.Parse(r("Credit Amount").ToString)).ToString,((Double.Parse(r("EMI").ToString)+(Double.Parse(dict(r("CIF Number").ToString))*0.05))/Double.Parse(r("SALARY").ToString)).ToString}).ToArray,False)).CopyToDataTable