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

[sharazkm33]

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

[sharazkm33]

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

[Yoichi]

Hi,

How about using Dictinoary as the following sample?

Sample20220126-5.zip (3.0 KB)

Regards,

[Srini84 (Srinivas)]

@sharazkm33

Check below for your reference

Linq query for groupby and sum

I have a datatable as below. I want to group by using Col3 and take sum of Col2. Input Datatable: Col1 – Col2 – Col3 – Col4 A – 11 – AA – XYZ B – 12 – RT – XYZ C – 13 – HL – XYZ D – 27 – AA – XYZ E – 17 – AA – XYZ F – 15 – RT – XYZ G – 24 – HL – XYZ OutPut required: Col1 – Col2 AA – 55 RT – 27 HL – 37 Please help me with the LINQ query. @ppr @arivu96 @ClaytonM

Hope this will help you

Thanks

[sharazkm33]

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

[sharazkm33]

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

[ppr (Peter)]

have a look here for some more trainings

[HowTo] Overview on different options for grouping data and processing the groups

This HowTo introduces on the different options for grouping data from a datatable in order to process the grouped data. Introduction Grouping data and processing the grouped data is a common scenario e.g. when the grouped data is to aggregate like summing up, find maximum, get the average or concatening items. Lets have a look on following data: [grafik] A possible scenario could be: Create a report containing following information: the region code the sum of CaseCount per RegionCode t…

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

[Yoichi]

Hi,

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

Sample20220126-5v2.zip (2.9 KB)

Regards,

[sharazkm33]

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

[sharazkm33]

@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

[Yoichi]

Hi,

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

Regards,

[sharazkm33]

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

[sharazkm33]

@Yoichi Please let me know if you need more details

[Yoichi]

Hi,

how about the following?

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

Sample20220126-5v2.zip (3.2 KB)