Query sum and group of the row

Help Studio
1

Hi,

Need your help and advise on how to get the output as below using linq query.
simplified query query. Using looping and if command take the process too long. I need to process more than 200 row items.

sample input

Location transaction invoiceno Status POno date1 Amount Term status
PJ Invoice AM-370721A Standard 1198335 23/7/2021 274560 N90 Unpaid
Invoice AM-370721 Standard 1196703 23/7/2021 89310 N90 Unpaid
Invoice JA-420821F Standard 1208100 13/8/2021 366300 N90 Unpaid
Invoice JA-420821E Standard 1199970 13/8/2021 165000 N90 Unpaid
Invoice JA-420826 Standard 1199970 13/8/2021 165000 N90 Unpaid

and sample output

Location transaction invoiceno Status date1 Amount Term status
PJ Invoice AM-370721 Standard 23/7/2021 89310 N90 Unpaid
Invoice JA-420821 Standard 13/8/2021 531300 N90 Unpaid
Invoice JA-420826 Standard 13/8/2021 165000 N90 Unpaid

attached sample excel file that need to process.sample input and output.xlsx (10.0 KB)

2

Edit:

  • first suggestion deleted due wrong interpretated requirement
  • corrected LINQ → below
  • requirement / sample update done by the author → below

Also have a look here (LINQ and GroupBy):

3

Hi the output supposed which delete the last char and sum up of it as below output.

Location transaction invoiceno Status date1 Amount Term status
PJ Invoice AM-370721 Standard 23/7/2021 36380 N90 Unpaid
Invoice JA-420821 Standard 13/8/2021 531300 N90 Unpaid
Invoice JA-420826 Standard 13/8/2021 165000 N90 Unpaid
4

working with a prepared dtResult:
grafik

grafik

(From d In dtData.AsEnumerable
Let ik = Regex.Replace(d("invoiceno").toString.Trim,"[A-Za-z]+$","")
Group d By k=ik Into grp=Group
Let sm = grp.Sum(Function (x) CInt(x("Amount").toString.Trim))
Let g = grp.First()
Let ra = New Object(){g(0), g(1), k, g(3), g(5),sm, g(7), g(8)}
Select dtResult.Rows.Add(ra)).CopyToDataTable

ensure: System.Text.RegilarExpressions is added to the imports

grafik

5

Thanks Peter, it works thanks again for your help :+1:

6

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