Trying to capture every filtered group

[pprin001 (Paolo70)]

Hello.

By the way, these values in the column “Key” are not fixed so It will change so the bot has to be able to do it for new values.

For example: In the future, There could be 2 values “E…1” and “E…2”. Next month : There could be3 values “E…1”, “E…2”, “E3” and so on.

It has to be dynamic.

I’d like to capture every group filtered by the column “Key”.

For example. I’ve filtered by a value in the “Key”
“ENT960054713”

Next, It would be “ENT960054714”

and so on for different values in the column “Key”

Could you help me automate this workflow in a xaml file.

Please and thanks.

I’ve uploaded the file with all the values and filtered it with one value but It has to be for every value in the column “Key” .

My main objective is later to count all the rows by each group filtered.

Please .

test1.xlsx (784.3 KB)

test1.xlsx (784.2 KB)

[Anil_G (Anil Gorthi)]

@pprin001

So if you need only the count of eqch group the. Use the below linq…which will give you a two column table one with your key and other with count…you can build a datatable with 2 columns and use the belao in assign

Newdt = (From d In Dt.AsEnumerable() Group d By k=d("key").toString.Trim Into grp = Group Let coun = grp.count Let ra = New Object(){k,coun} Select r = Newdt.Rows.Add(ra)).CopyToDataTable()

If you want each of the group only as separate sheets or anything then

1. Use a assign with uniquedt = dt.DefaultView.ToTable(True,"key") - this will give to the unique values from key column
2. Now use for eqch row in datatable on uniquedt
3. Inside the loop use filter datatable and filter the original dt on key column with currentrow(0).ToString - for each iteration one group will be filtered which you can write to excel in different sheets or append or use merge datatable and create a merged table again

Hope this helps

Cheers

[pprin001 (Paolo70)]

@Anil_G

(From d In Dt.AsEnumerable() Group d By k=d(“key”).toString.Trim Into grp = Group Let coun = grp.count Let ra = New Object(){k,coun} Select r = DtClone.Rows.Add(ra)).CopyToDataTable()

I was trying to set up the sentence but I’m getting different mistakes…

could you upload a xaml.file that works with the excel file to analyze it, please?

[Anil_G (Anil Gorthi)]

@pprin001

Dtclone should be newdt …please change that and newdt is a datatable

Cheers

[pprin001 (Paolo70)]

@Anil_G

Newdt

Hello.

I got this message now.

Could you help me , please.

Thanks.

[Anil_G (Anil Gorthi)]

@pprin001

Did you change newdt to datatable…i guess it is of string type presently

@Anil_G

you can build a datatable with 2 columns

You need to build a datatatble

Cheers

[pprin001 (Paolo70)]

@Anil_G

count

Hi.

I created the datatable

but I got this error

How could I solve it, please.

[Anil_G (Anil Gorthi)]

@pprin001

Did you build the datatable?

Error says either of the datatables are not initialized or are null

Cheers

[pprin001 (Paolo70)]

yes, I did .

[Anil_G (Anil Gorthi)]

@pprin001

When running in debug once check if nevudt is also having data…

Cheers

[pprin001 (Paolo70)]

Hello.

It works but I’m trying to add a new column with the sum of each value is that possible, please. ?

for example EEEE ; 12
IIIII ; 42
bbbb; 3

It works, thanks.

(From d In nuevooDT.AsEnumerable() Group d By k=d(“key”).toString.Trim Into grp = Group Let coun = grp.count let xSum=grp.Sum(Function(x) Cdbl(x(“count”).ToString.Trim)) Let ra = New Object(){k,coun,xSum} Select r = Newdt.Rows.Add(ra)).CopyToDataTable()