Compare and delete the specific rows that give zero

Help Studio
, ,
1

I need to compare several columns - subtract rows - and delete those rows that return zero and keep remaining.
The most rows are duplicates, but fx. ID 30 is not.

I have tried with this linq query, but the result is not quite, what I need. The ID 30 does not appear at all, due to group by!

(From x In dttemp Group x By a = x(“ID”).ToString.trim Into grp = Group Select
dttemp.Rows.Add({a,grp.sum(Function(k) CInt(k(“Value”).Tostring))})).CopyToDataTable

Before:

image
image444×540 50.3 KB

Result:
image

Need this:
image

Thanks.

2

Hi @renshatfar

Please see the below. That should solve

(From x In dt.AsEnumerable 
Group x By a = x("ID").ToString.trim Into grp = Group 
Let b = grp.Select(Function(x) CInt(x("Value").ToString)).
Take(CInt(grp.Select(Function(x) CInt(x("Value").ToString)).Count/2)).Sum(Function(x) CInt(x))
Let ra = New Object(){a, b}
Select dt.Rows.Add(ra)).Where(Function(x) Not CInt(x("Value").Tostring).Equals(0)).CopyToDataTable

cheers

3

Thanks for your response Anil G.

Unfortunately it doesn’t work. I get this as result:

result2

but I need this:

after

4

Hi @renshatfar

You need 20 to be repeated twice? .where as others only once? Why is that?

And are you filtering the data before this? can you check your datatable if it has all rows before performing this step

and please try this

(From x In dt.AsEnumerable 
Group x By a = x("ID").ToString.trim Into grp = Group 
Let b = String.Join("||",grp.Where(Function(x) Not x("Value").ToString.Contains("||")).
Select(Function(x) x("Value").ToString)).Split("||",StringSplitOptions.None).Take(CInt(String.Join("||",grp.Where(Function(x) Not x("Value").ToString.Contains("||")).
Select(Function(x) x("Value").ToString)).Split("||",StringSplitOptions.None).Count-1)).Sum(Function(x) CInt(x.ToString))
Let ra = New Object(){a, b}
Select dt.Rows.Add(ra)).Where(Function(x) Not CInt(x("Value").Tostring).Equals(0)).CopyToDataTable

Input
image

Output
image

Cheers

5

Hi Anil

I can settle for that ID 20 does not stands separate and I don’t any datatable filtering.
That’s the flow:

flo
flo1037×452 24.4 KB

And getting this error:

Compiler error(s) encountered processing expression “(From x In dt.AsEnumerable Group x By a = x(“ID”).ToString.trim Into grp = Group Let b = String.Join(”||“,grp.Where(Function(x) Not x(“Value”).ToString.Contains(”||“)).Select(Function(x) x(“Value”).ToString)).Split(”||“,StringSplitOptions.None).Take(CInt(String.Join(”||“,grp.Where(Function(x) Not x(“Value”).ToString.Contains(”||“)).Select(Function(x) x(“Value”).ToString)).Split(”||“,StringSplitOptions.None).Count-1)).Sum(Function(x) CInt(x.ToString))Let ra = New Object(){a, b}Select dt.Rows.Add(ra)).Where(Function(x) Not CInt(x(“Value”).Tostring).Equals(0)).CopyToDataTable”.

Overload failed because there is no available ‘Split’ that can be called with these arguments:
‘Public Function Split(separator() As String, options As System.StringSplitOptions) As String()’: A value of type ‘String’ cannot be converted to ‘1-dimensional array of String’.

Thanks again.

6

Hi @renshatfar

Can you please replace all inverted commas and see …may be its because of version differenc

Cheers

7

Hi Anil

I don’t think it has anything to do with the comma. There was no problem running your first query.

syn
syn1688×240 20.5 KB

Which version do you have?

I have 2021.4.4 and I can upgrade my Studio if necessary.

And this the version of dependencies:

depen

I really need this to work.

8

Hi, would 20 - 180 instead of 20 - 100 and 20 - 80 be valid?

9

Hi @renshatfar

I guess you are using windows-legacy, so just include curly braces as below(in split string). Below code should be compiled properly

(From x In dt.AsEnumerable 
Group x By a = x("ID").ToString.trim Into grp = Group 
Let b = String.Join("||",grp.Where(Function(x) Not x("Value").ToString.Contains("||")).
Select(Function(x) x("Value").ToString)).Split({"||"},StringSplitOptions.None).Take(CInt(String.Join("||",grp.Where(Function(x) Not x("Value").ToString.Contains("||")).
Select(Function(x) x("Value").ToString)).Split({"||"},StringSplitOptions.None).Count-1)).Sum(Function(x) CInt(x.ToString))
Let ra = New Object(){a, b}
Select dt.Rows.Add(ra)).Where(Function(x) Not CInt(x("Value").Tostring).Equals(0)).CopyToDataTable

cheers

10

Hi Anil

I no longer get syntax errors. Thanks
But I’m still not getting the right result just like yours. :frowning:

nyr

image

11

Hi @renshatfar

Can you check if you are giving the same dt…Because I see a total different output. Can you send your xaml if you can…That might help for resolving it

cheers

12

Here you are:
groupBy.zip (8.3 KB)

Maybe it has to be done in several steps!

13

Hi s0biesky

Sorry I haven’t seen your post. Yes, it can work, but it’s not quite optimal.

14

Hi @renshatfar

I ran the bot and it did append the required output.

image

can you please check if you are checking the right file. I did not change anything in your xaml Except the “Path to Excel” to “text.xlsx”

I even ran with this and its working can you check

(From x In dt.AsEnumerable 
Group x By a = x("ID").ToString.trim Into grp = Group 
Let b = grp.Sum(Function(x) CInt(x("Value").ToString)).ToString
Let ra = New Object(){a, b}
Select dt.Rows.Add(ra)).Where(Function(x) Not CInt(x("Value").Tostring).Equals(0)).CopyToDataTable

cheers

15

Hi Anil

It works perfectly :ok_hand: and I greatly appreciate your help.

1 Like
16

Hi Anil

Unfortunately, I have not solved the problem.
I actually have several rows in the Excel file and that is why I fx. needed ID 20 to stand separate.

Whether Spain or France comes out, it does not matter. It is important to include the columns country and code in the result.

before1

I have tried with this query but I get this error:

(From x In dt.AsEnumerable
Group x By a = x(“ID”).ToString.trim Into grp = Group
Let b = grp.Sum(Function(x) CInt(x(“Value”).ToString)).ToString
Let c = grp.Select(Function (x) x(“Country”).toString.Trim).ToString
Let d = grp.Select(Function (x) x(“Code”).toString.Trim).ToString
Let ra = New Object(){a, b, c, d}
Select dt.Rows.Add(ra)).Where(Function(x) Not CInt(x(“Value”).Tostring).Equals(0)).CopyToDataTable

error
error1438×419 56.9 KB

The result can be either this:

result5

or this:
result6

I hope you have time to look at it.

Thanks again.

17

@renshatfar

If you want to group by those columns use like this

(From x In dt.AsEnumerable
Group x By a = x(“ID”).ToString.trim,c = x(“Country”).ToString.trim,d = x(“Code”).ToString.trim Into grp = Group
Let b = grp.Sum(Function(x) CInt(x(“Value”).ToString)).ToString
Let ra = New Object(){a, b, c, d}
Select dt.Rows.Add(ra)).Where(Function(x) Not CInt(x(“Value”).Tostring).Equals(0)).CopyToDataTable

else you want to only group on ID then use this

(From x In dt.AsEnumerable
Group x By a = x(“ID”).ToString.trim Into grp = Group
Let b = grp.Sum(Function(x) CInt(x(“Value”).ToString)).ToString
Let c = grp.Select(Function (x) x(“Country”).toString.Trim).FirstOrDefault
Let d = grp.Select(Function (x) x(“Code”).toString.Trim).FirstOrDefault
Let ra = New Object(){a, b, c, d}
Select dt.Rows.Add(ra)).Where(Function(x) Not CInt(x(“Value”).Tostring).Equals(0)).CopyToDataTable

cheers

18

Thank you so much for your help Anil.

1 Like
19

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