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

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

In general it looks like a Group By Case

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

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.

Thank you. I will try this.

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

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

This worked! Thank you!

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

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

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