Linq Statement for Pivot Table Mapping

[Tarif_Mohammad (Parth)]

Hello Every One , I haev the following pivottable:

My Task is to sum the row value of column D “Wert/BWähr” only when the respective row value of column “Auftrag” , “Nebenkontierung”, “Kostenart” are the same. So just like u can see in the SS that in first green marked box only the first three row values from column “Wert/BWähr” will be summed up. Next the blue one and the orange one.
So to summerise, for each row check if the columns “Auftrag” , “Nebenkontierung”, “Kostenart” have the same value then add the values of column Wert/BWähr" for the same row to a sum variable and write the sum. at the end the table should look like this with the sum, for each row :

I am understandig the logic but finding difficulties to implement this using proper statement. Can anyone please help me with this. Would be highly appreciated

Thanks in advance

[ashwin.ashok ((TheCodingTheory))]

@Tarif_Mohammad

Wert/BWähr

Hi @Tarif_Mohammad ,

This is something I came up with on the spot, so I can’t gurantee that it will work 100% but its worth a try.

First, declare a DataTable → dt_result that will hold the final output using a Build DataTable Activity.

Then, use an Assign Activity and pass this snippet of code into it:

Dt.AsEnumerable().GroupBy(Function(g) Tuple.Create(g("Nebenkontierung 1").ToString,g("Kostenart").ToString)).Select(Function(s) Dt.Clone.LoadDataRow(s.First().ItemArray.Take(3).Append(s.Sum(Function(su) Convert.ToDouble(su("Wert/BWähr").Tostring.Trim))).ToArray,False)).CopyToDataTable()

PivotTableConversion.xaml (7.2 KB)

If that doesn’t work, then we would appreciate it if you could provide us with some sample data to work with.
That would put us in a much better position to assist you.

Kind Regards,
Ashwin A.K

[Tarif_Mohammad (Parth)]

@ashwin.ashok Thanks a ton for the immediate response I will give ur solution go and will keep u updated via this thread later on. Till Then

[Tarif_Mohammad (Parth)]

Hi @ashwin.ashok I tried ur workflow and i ma somehow going through error which i m not being able to solve. Could u please help me further?

Here are some extra details:

Beispiel Werteermittlung CO-Umbuchung KOA 80000000.XLSX (17.2 KB)
this is the excel and it should actually look like this at the end. u can open this and use it too.

and what i currentlly have is the following:

Tst_Kostenstelle.XLSX (14.9 KB)
this has to look like the above excel after using the logic that i mentioned in the thread

I appreciate ur time and effort . would be a great help if u can help me out here

[ashwin.ashok ((TheCodingTheory))]

Hi @Tarif_Mohammad ,

Could you try this and see if it works out for you?

Please validate whether the calculation is accurate, I have set the operations to add values as they are i.e., if there are any negative values, it will get substracted from the whole.

Dt.AsEnumerable().GroupBy(Function(g) Tuple.Create(g("Auftrag").ToString,g("Nebenkontierung_1").ToString,g("Kostenart").ToString)).Select(Function(s) dt_result.Clone.LoadDataRow(s.First().ItemArray.Take(3).Concat({s.Sum(Function(su) Convert.ToDouble(su("Wert/BWähr").Tostring.Trim)),s.First().ItemArray.Last()}).ToArray,False)).CopyToDataTable()

This is the logic for the summation at the last line which is passed into a Add DataRow Activity:

{"Gesamtergebnis","","",dt.AsEnumerable().Sum(Function(su) Convert.ToDouble(su("Wert/BWähr").ToString.Trim)),""}

PivotTableConversion_v1.xaml (11.8 KB)

Kind Regards,
Ashwin A.K

[Tarif_Mohammad (Parth)]

@ashwin.ashok Hi Ashwin It just works perfectly. Thanks a ton again for the help . This made my day. Thanks you so much -Cheers !!!