Extract duplicates and filter

Help Forum
1

Hi there, I currently have a datatable Table1 where I have columns
ID, Amt
ABC, 123
ABC, 123
TUV, 456
TPO, 0
TPO, 0

What I want to do is to extract only the duplicated rows where Amt does not equal to 0.

I currently have an assign activity for my output dt being Table 2 =
Table1.AsEnumerable.GroupBy(function(r) r(“ID”)).Where(function(r) r.Count() > 1).SelectMany(function(l) l).ToArray().CopyToDatatable

Current output
ID, Amt
ABC, 123
ABC, 123
TPO, 0
TPO, 0

This gives me a datatable with all duplicated rows inside (correct) - but how do I add the condition to the above to specify that only the columns whre Amt =/= 0 is brought over?

Desired Output
ID, Amt
ABC, 123
ABC, 123

Would appreciate someone’s help on how to modify my formula so I can extract the above. Thank you!

2

You can just use a filter data table to the Table 2 and remove rows where amount is != 0.

3

Hi, just to confirm this means

Table2 = my duplicate rows where Amt =0 or != 0
Then I do a filter table to remove the rows where Amt = 0?

But does that mean that I’ll completely lose the duplicate rows where Amt = 0? As I want to have these kept in Table1 as I need these later on.

4

You can take 2 approaches.

  1. you can apply filter on Table 2, so you are only filtering on duplicate rows and you are not losing any original data.
  2. You can use filter data table on Table 1 and save the output (filtered table) to a different data table (Dt_Temp). Then you can use the formula on DT_Temp (as DT_Temp is filtered it wont have 0s). So you have original data in Table 1 itself.
5

Hi @cookiedough

Give a try with

Table1.AsEnumerable().GroupBy(Function(r) r("ID")).Where(Function(g) g.Count() > 1 AndAlso g.Any(Function(r) Convert.ToInt32(r("Amt")) <> 0)).SelectMany(Function(g) g).CopyToDataTable()

Regards!