How to get count values based on the values in the other columns in a Datatable in Linq Query? Please Help

Help
1

Please help me to solve this.
In a separate datatable, I need to get the count values (“X”, “Y” & “Z”) in “Column 2” in Datatable 1 for each values in “Column 1”.
Also I need to get count values (“X”, “Y” & “Z”) in “Column 2” where “Column 3” value is not equal to “T”.
GroupByCount.xaml (11.8 KB)

What I have:
Datatable 1 (DT1):
image

What I need in a separate Datatable (OutputDT):
image

Since dealing with datatable with large data, I am trying in LinQ Query instead of For Each loop:

Please help me

@ppr @supermanPunch

2

@GuhanTM If you can provide the input data in an excel file, we may be able to provide you the solution faster :sweat_smile: Also are those values fixed like will it be always the values X, Y, Z, F, R, T, M appearing in the table ? or will it vary ?

3

Hi @supermanPunch
Attached the input excel file.Input.xlsx (8.3 KB)

4

@supermanPunch The values mentioned will vary. But we need to get count of three values (X,Y,Z). I will substitute X,Y, Z with actual value in final version.

5

@GuhanTM Do you need only Count of three Values X, Y, Z so the other values are not needed right ? and hence according to the values you will only need 3 columns for X_Count, Y_Count and Z_Count ?

6

@supermanPunch The actual values and calculation will be like
X will be substituted with text “X” (count of X)
Y will be substituted with text “Y”, “M”, “N” (count of Y + Count of M + Count of N)
Z will be substituted with text “Z” (count of Z)

So we should not remove the values other than “X”,“Y” and “Z” from the column

7

@GuhanTM Check this workflow, I’m not entirely sure if that is what you needed, please explain more if that’s not the output you need :
GroupByCount.zip (9.2 KB)

8

@GuhanTM
Let’s enter the first iteration with this starter help:
GuhanTM_GroupByColSetCounts.xaml (9.3 KB)

input: As by your excel
output:

Col1 X_Count Y_Count Z_Count X_Count_NE_T Y_Count_NE_T Z_Count_NE_T
A 2 1 1 2 0 0
B 1 2 1 1 1 1
C 1 1 2 1 0 2
D 0 0 0 0 0 0

let us know your open questions on the statement and your feedback.
Thanks

1 Like
9

Thank you @ppr
Its working as expected.
Thanks again

10

Thanks @supermanPunch

11

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