Check Duplicate and remove

[Gokul_Murali]

Hello guys,

First i need to check for the “original policy number” column if it is same it should Sum up the “net” column value and if the net is zero i want to remove both the row in the same excel.

w

[Sanjay_Bhat]

Hi @Gokul_Murali

Check out this linq expression

dt_data=dt_data.AsEnumerable().
    GroupBy(Function(row) row("Original Policy Number").ToString()).
    Where(Function(g) g.Sum(Function(row) Convert.ToDouble(row("Net"))) <> 0).
    SelectMany(Function(g) g).
    CopyToDataTable()

Hope this helps !

[dkollyns (Diógenes Kollyns)]

Hi @Gokul_Murali
Try this way:

1.Read the Excel file using Read Range.
2.Use LINQ to group data by original policy number and sum the net column ASSIGN ACTIVITY:
groupedData = dataTable.AsEnumerable().
GroupBy(Function(row) row(“original policy number”).ToString.Trim).
Select(Function(group) New With {
Key .PolicyNumber = group.Key,
Key .NetSum = group.Sum(Function(row) Convert.ToDecimal(row(“net”)))
}).ToList()

3.Identify policy numbers with a total net of zero ASSIGN ACTIVITY.
policiesToRemove = groupedData.Where(Function(group) group.NetSum = 0).
Select(Function(group) group.PolicyNumber).ToList()

4.Remove rows with zero net using either ASSIGN ACTIVITY:
filteredData = dataTable.AsEnumerable().
Where(Function(row) Not policiesToRemove.Contains(row(“original policy number”).ToString.Trim)).
CopyToDataTable()

5.Write the updated data back to the Excel file using Write Range.

[Gokul_Murali]

@Sanjay_Bhat

In this excel i forgot to tell you see the headers is in 2 rows so how can i mention the column name in that code.
since this is a fixed template we can’t change it

So is there any other way

[Ajay_Mishra (Ajay Mahendra Mishra)]

Hey @Gokul_Murali

Use this LinQ: Changed a bit

dt_data= dt_data.AsEnumerable().Skip(1).GroupBy(Function(row) row("Original Policy Number").ToString).Where(Function(g) g.Sum(Function(row) Convert.ToDouble(row("Net"))) <> 0).SelectMany(Function(g) g).CopyToDataTable()

Regards,
Ajay Mishra

[Sanjay_Bhat]

Hi @Gokul_Murali
If its fixed template while using read range activity you can specify the range from the headers that is ex Excel.Sheet("Sheet2").Range("A2")
Or
if You can show how your datatable looks like after reading it will be easier

Thank you

[Gokul_Murali]

@Sanjay_Bhat

I have used “A7” row so it will take “Net” column name but in the next row of net column is empty

Iam getting error like this

[Sanjay_Bhat]

Try this

dt_data = dt_data.AsEnumerable().Where(Function(x) Not String.IsNullOrEmpty(x("Net").ToString))


    dt_data =dt_data.AsEnumerable.GroupBy(Function(row) row("Original Policy Number").ToString()).
    Where(Function(g) g.Sum(Function(row) Convert.ToDouble(row("Net"))) <> 0).
    SelectMany(Function(g) g).
    CopyToDataTable()

or if only first two lines are empty

dt_data.AsEnumerable().Skip(2).
    GroupBy(Function(row) row("Original Policy Number").ToString()).
    Where(Function(g) g.Sum(Function(row) Convert.ToDouble(row("Net"))) <> 0).
    SelectMany(Function(g) g).
    CopyToDataTable()

[Gokul_Murali]

@Sanjay_Bhat

When i use the above code its not showing any error but not removing the duplicate value

My output

[Sanjay_Bhat]

Check this
ForumSolutions.zip (9.7 KB)

[Gokul_Murali]

@Sanjay_Bhat

Document is showing invalid brother

[Sanjay_Bhat]

Input:

Output

Code:

[sudster]

Hi @Gokul_Murali , please try if you find this easier…

Input:

Process:

Result:

[Gokul_Murali]

@Sanjay_Bhat

It is showing error like this

[Gokul_Murali]

@sudster

Bro expressions are not clear in this image

[sudster]

@Gokul_Murali , here’s the xaml

Sequence.xaml (19.9 KB)

[Gokul_Murali]

@sudster

Document is showing invalid

[sudster]

@Gokul_Murali , go into Manage Packages and update all of them.

My Studio version is 2023.10.6

[Gokul_Murali]

@sudster

Only this flow is available in this xaml file

[sudster]

@Gokul_Murali , you are looking at a totally different xaml my friend. Close all your Studio files. Start a new Studio and open the attached xaml file, followed by updating all packages.