Check duplicate values in a column and update the status

Hi Team,

I have a column name category which has 2 values Purchase invoice and payments.

Now we have to use the combination of Purchase invoice and payments for invoice column which have same invoice like (1,1) and sum of amount column will be 0 (50,-50) then update the status value as match.

Below is the attached output screenshot

Note - As you can see for invoice 6 we have 2 payments in that case we have to consider the 2nd payment with purchase invoice.

Anyone, please help :smiley:

Along with some chance to customize / hybride approach with LINQ you can do it with grouping the data and processing the group member by this filter approach:


taken from: [HowTo] Overview on different options for grouping data and processing the groups

Could you please provide me the workflow or step-by-step solution as per my question requirement, so that I can understand the process?

@Anil_G Can you please help me

If possible can any help me to crack this logic.

just start with main building block

  • Assign: dtDistinct = YourDataTableVar.DefaultView.ToTable(True, {“desciption”})

    • note the col name typo from screenshot
  • For each row activity | CurrentRow in dtDistinct

    • Filter DataTable
    • Comment Activity - set Breakpoint

Debug it and trace the flow so far within the debugging panels
Understanding the 6 Debugging Panels of UiPath in the easiest way possible! - News / Tutorials - UiPath Community Forum

Within a next iteration we can focus on the group member processing - the result of the filter datatable

If possible Could you please provide me the workflow or step by step process because i am new to UiPath and don’t have much knowledge about it.

@raja.arslankhan @Nitya1 Can you please help me out :pray:t2:

@Shivam_Rana

Please follow the steps

  1. Read the data into a datatable dt
  2. Use for each row in datatable activity with dt and assign a indexvar to the index in for loop properties
  3. Use if condition with currentRow("Category").ToString.Equals("Purchase Invoice")
  4. On then side use filter datatable activity and filter the dt on column description with currentRow(“description”).ToString and save to filtereddt
  5. Use if condition on then side only with currentrow("Amount").ToString.Equals(filtereddt.AsEnumerable.Last()("Amount").ToString)
  6. On the then side of current if Use assign with currentrow("Status") = "Match" and also another assign with dt.Rows(dt.Rows.IndexOf(filtereddt.AsEnumerable.Last()))("Status") = "Match"

After the loop you can write the data back

Hope this helps

Please try

Cheers

Hi @Anil_G

I am getting this error in assign activity and even though filteredDt variable only have 1 row count.
Please use the attached screenshot for your reference.







@Shivam_Rana

Is it possible to attach the xaml and input file here please

Filtereddt …please check the filters ideally it shpuld not be 1

And try changing expression for assign like this

dt.Rows(dt.Rows.IndexOf(dt.AsEnumerable.Last(function(x) x("description").ToString.Equals(currentrow("description").ToString))))("Status")

Cheers

TestNewDataJE.xlsx (10.0 KB)
Main.xaml (16.3 KB)
Please use this for your reference

@Anil_G
I have also created one xaml file which perform almost accurate operation the only problem is it doesn’t contain the combination of purchase invoice and payment and it contains the combination of purchase invoice along with all payments.
O/P-


JEInvoice.xaml (20.3 KB)
The O/P should be like this-

@Shivam_Rana

thsi si the output

made few modifications

newdata.xaml (16.5 KB)
TestNewDataJE.xlsx (9.9 KB)

cheers

thats great :smiley:
But i don’t know why but my output is different from yours .
it is showing 300 and 300 are match values.

1 Like

@Shivam_Rana

Can you download again…I guess you downloaded it immediately…and I changed a bit after in one if activity where you would see math.abs…changed it to add and check total

Cheers

1 Like

you are awesome @Anil_G :grin:
Got the correct outputđź‘Ť
Thanks for your help

1 Like

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