Filter with 2 Condition

Help StudioX
,
1

Hello there, i have question about my use case.

I want to filter NO GE Deviation data that only has CLOSED status, if there is an Open status in NO GE Deviation, it will not be included in the filter. I have tried several filter activities but none of them worked. Is there anyone who is willing to help to create the flow in the UI Path StudioX? thanks in advance :blush:

here is my example table :
data excel

2

Hi @Muhammad_Alfiannur_Maruf

Use Filter Datatable Activity

image

Regards,

3

@Muhammad_Alfiannur_Maruf

I am attaching below workflow for better understanding.

image
image697×399 12.3 KB

image
image

Regards,
Chandrika.

4

Hi @Muhammad_Alfiannur_Maruf

Try below approach

  1. Read Excel File using Read Range Activity
  2. Use Filter Data Table Activity
    Select Keep and Set Status as Column Name & Value as CLOSED

image
image752×641 28.9 KB

Hope it will helps you :slight_smile:

Happy Automation

5

@Muhammad_Alfiannur_Maruf
Main.xaml (8.1 KB)
check the Xaml file
New Microsoft Excel Worksheet.xlsx (9.9 KB)

6

Hi Chandika, thanks for the kind response! I really appreciate your help! But the filter I mean is not that simple, because in the NO GE Deviation there are several data, if one of the data in the same NO GE Deviation has an Open status, it is not filtered… But thank you so much!!

7

@Muhammad_Alfiannur_Maruf
Linq Query:
filterdata=dt_data.AsEnumerable().Where(Function(row) row.Field(Of String)("Status").ToUpper() = "CLOSED").CopyToDataTable

filter1

InputFile
ipFIle

Output File:-
opfile

8

@Muhammad_Alfiannur_Maruf

With Same NO GE DEVIATION 1 is having OPEN And 2nd having CLOSED input file.
ip2File

Output File.
op2File

Flow with LINQ
filter1

9

You will need to do something like this:

  • Read Range into DT_Main
  • For Each Row in DT_Main.DefaultView.ToTable(true,“No GE Deviation”)
    ** Filter Data Table into DT_Temp with keep rows set to “No GE Deviation” = CurrentRow(“No GE Deviation”).ToString AND “Status” = “OPEN”
    ** If DT_Temp.Rows.Count = 0
    *** Then Filter Data Table into DT_Temp with keep rows set to “No GE Deviation” = CurrentRow(“No GE Deviation”).ToString
    *** Merge Data Table DT_Temp into DT_Final

The logic here is to loop through the unique values of No GE Deviation and check if there are any that are OPEN. If there are no rows OPEN, then get all the rows for that No GE Deviation and copy them into DT_Final.

The .DefaultView.ToTable expression gets you the unique values for that column to loop through.

After it runs, DT_Final will have all the rows for the No GE Deviation values that do not have an OPEN status in any of the rows.