Datatable summing of above rows based on condition

[Ritika_Singh]

Hi All,
I have an excel sheet with 83+ columns and out of these I need to work on few columns create a data , here is the input

Layer	Type	Company	Value
1	T	a	100
1	F	b	200
2	T	c	300
2	F	d	400
3	F	e	500
3	F	f	600
The expexted output is:
Output
—	—	—	—	—
Layer	Type	Company	Value	Combined
1	T	a	100
1	F	b	200	300 XS of 100
2	T	c	800
2	F	d	400	1200 XS of 300
3	F	e	500	1100 XS of 1500
3	F	f	600	1101 XS of 1500
So here I need to consider the layers and Type columns for my decesion to sum up the value column and create a combined data colum. My logic is that I want to sum the current layers in XS of the above rows. For eg for layer 2 (400+800) in XS of (200+100)
For Layer 1 (200+100)in XS of 100 , for the Type as T I donot need to have any combine data row I need to consider its value , For Layer 3 (600+500) in XS of (400+800+200+100)

Please if any one of u can suggest something , I tried by for loop bt its not working fine

[Ritika_Singh]

@Yoichi , Please any help from your end.
Thanks in advance

[ppr (Peter)]

looks like a groupby case. An introduction to this (NON-LINQ, LINQ Approaches) find here:

[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…

[Yoichi]

Hi,

How about the following?
I think the first combined value should be “300 XS of 0”. If it’s wrong, can you share the way of calculation?

Dim t As Int32 =0
dt.AsEnumerable.GroupBy(Function(r) r("Layer").ToString).ToList.ForEach(
Sub(g) 
    Dim s As Int32 = g.Sum(Function(r) CInt(r("Value").ToString))
    g.Tolist.ForEach(
    Sub(r)
        If (r("Type").ToString="F") Then
            r("Combined")= s.ToString+" XS of "+t.ToString
        End If
    End Sub
    )
    t=t+s
End Sub
)

Regards,

[Ritika_Singh]

Hi Yoichi,

For the first layer the value in XS of will be the first value of the row with type T i.e 100

[Ritika_Singh]

The code file that you have shared is not openening can you please share it again.

Thanks in advance

[Yoichi]

Hi,

How about the following?

Dim t As Int32 =CInt(dt.Rows(0)("Value").ToString)
Dim isFirst As Boolean = True
dt.AsEnumerable.GroupBy(Function(r) r("Layer").ToString).ToList.ForEach(
Sub(g) 
    Dim s As Int32 = g.Sum(Function(r) CInt(r("Value").ToString))
    g.Tolist.ForEach(
    Sub(r)
        If (r("Type").ToString="F") Then
            r("Combined")= s.ToString+" XS of "+t.ToString
        End If
    End Sub
    )
    If (isFirst) Then
	    t=s
    Else
        t=t+s
    End If
End Sub
)

Sample20230220-1aL.zip (2.9 KB)

Regards,

[Ritika_Singh]

Thank you so much , I will try this and get back!!

[Ritika_Singh]

@Yoichi

(r)
        If (r("Type").ToString="F") Then
            r("Combined")= s.ToString+" XS of "+t.ToString
        End If
    End Sub
    )
    If (isFirst) Then
	    t=s
    Else
        t=t+s
    End If
End Sub
)

This is not returning the correct sum of above layers

[Yoichi]

HI,

Sorry,I forgot to change flag value. How about the following? Is this result correct?

If (isFirst) Then
	t=s
	isFirst = False
Else
    t=t+s
End If

Sample20230220-1aLv2.zip (2.9 KB)

Regards,

[Ritika_Singh]

Thank you