Compare 2 rows of the same column and select one row if another column fulfils a certain criteria

[T.S]

I am new to studio
SCENARIO: I have a 100 rows and 13 columns in a data table. How can i check the values of row 1 and 2 or row 7 and 8 as an example to see if they contain the same value?
If they do contain the same value, check to see if they contain the same amount in column 5. One row will have a negative and the other row will have a positive but it will be the same figure.
Split the rows that have a negative value in one data table and those with positive in another.
Thanks

[Anil_G (Anil Gorthi)]

@T.S

Welcome to the community

can you please elaborate…is it like you need to check the first and lat row are same and so on?

or get the similar rows and check for negative and positive

cheers

[ppr (Peter Preuss)]

In general it looks like a Group By Case

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

Where we can group the data by Col13 and later Process Split the Group Members

We would also recommend to specify

• when not the pos/neg pair is found
• like in your ABC case more neg as pos members

[T.S]

Thanks for responding.
I’d like to check for similar rows based on column 13 then only keep the rows that have the negative value in column 5
So that would be the 3 rows with ABC but I’d only keep the 2 rows with the -4000
Same with the rows that have A34, I only want to end up with the row that has -70000

I hope this is a little clearer.

[T.S]

Thank you. I will try this.

[Anil_G (Anil Gorthi)]

@T.S

Edit: Added CDBL inside Math.ABS

try this in assign

Positive values dt.AsEnumerable.GroupBy(function(x) x(12).ToString + Math.abs(CDBL(x(4).ToString)).ToString).Where(function(x) x.Count>1).SelectMany(function(x) x).Where(function(x) CDBL(x(4).Tostring)>0).CopyToDatatable

negatice values dt.AsEnumerable.GroupBy(function(x) x(12).ToString + Math.abs(CDBL(x(4).ToString)).ToString).Where(function(x) x.Count>1).SelectMany(function(x) x).Where(function(x) CDBL(x(4).Tostring)<0).CopyToDatatable

cheers

[T.S]

I am getting the error below
"Argument ‘Value’:BC30518:Overload resolution failed because no accessible ‘abs’ can be called with these arguments

Could you shed some more light on what your suggested line does?
Thanks

[Anil_G (Anil Gorthi)]

@T.S

Edited the formula above …please use the same

cheers

[T.S]

@Anil_G

dt.AsEnumerable.GroupBy(function(x) x(12).ToString + Math.abs(CDBL(x(4).ToString)).ToString).Where(function(x) x.Count>1).SelectMany(function(x) x).Where(function(x) CDBL(x(4).Tostring)<0).CopyToDatatable

This worked! Thank you!
I would like to fully understand this as a newbie if you can help in any way.

[Anil_G (Anil Gorthi)]

@T.S

Glad it worked

Let me break it down

dt.AsEnumerable.
This converts datatable to Enumerable or kind of list of datarows

GroupBy(function(x) x(12).ToString + Math.abs(CDBL(x(4).ToString)).ToString)
This is to group the datarows with column 13 and absolute value of column 5(When I say absolute considering the value without positive or negative)

.Where(function(x) x.Count>1)
From the grouped rows consider only those rows where more than one row is present in the group

.SelectMany(function(x) x)
From the filtered groups select all the rows again

.Where(function(x) CDBL(x(4).Tostring)>0).CopyToDatatable
From all the rows obtain the Positive Column 5 Rows and create a datatable

Similarly for negative

Hope this helps

cheers