Filter by config data

[Jeeru_venkat_Rao]

Hello Friends,

I want to apply filter by config file.
See below screenshot:-

In above screenshot, I am applying filter in Reason column.
I want to filter only - “Decline do no retry, do not retry, invalid card & card expiry” from config and it may increase or decrease. and paste the data into output sheet. See the sample file.

How to apply this, pls help.
DeclineMethod.xlsx (8.9 KB)

[naveen.s]

@Jeeru_venkat_Rao ,

To apply a filter based on entries from a configuration file in UiPath, you can follow the steps below. This approach assumes you have a basic understanding of UiPath and can manipulate Excel files within it. Here’s a guideline to filter the data based on specific “Reason” values and copy the relevant rows to an output sheet.

Steps to Implement in UiPath:

1. Create a Configuration File:

   • Use a CSV or Excel file to store the reasons you wish to filter by. For example, your configuration file can have a single column with the values:

     Decline Do not retry
     Do not retry
     Invalid card
     Card Expiry
     

2. Create a New UiPath Project:

   • Open UiPath Studio and create a new process.

3. Read the Configuration File:

   • Use the Read CSV or Read Range activity to read the reasons from your configuration file.
   • Store the results in a DataTable (e.g., configReasonsDT).

4. Read the Input Excel File:

   • Use Excel Application Scope to open the input spreadsheet.
   • Use Read Range to read the sheet that contains the “Case,” “Patient Name,” and “Reason” columns into another DataTable (e.g., inputDT).

5. Filter the Data:

   • Use a Filter Data Table activity to retain only the rows with the “Reason” that matches those in your configReasonsDT. Here’s how to configure it:
     • Input DataTable: inputDT
     • Output DataTable: create a new DataTable (e.g., filteredDT)
     • Filter Mode: Use “Keep” to keep the rows matching the condition.
     • Filter Condition: Use the condition like Reason in configReasonsDT for each of the entries you wish to keep. You can use a loop if needed to dynamically build the condition.

6. Write the Filtered Data to an Output Sheet:

   • After filtering, go back to the Excel Application Scope and use the Write Range activity to write filteredDT to your output sheet.

Example Workflow Snippet

Here’s a simple outline of what the workflow might look like:

1. Read CSV/Excel (configReasons)
    - Output: configReasonsDT
2. Excel Application Scope
    - Read Range (InputData)
        - Output: inputDT
    - Filter Data Table
        - Input: inputDT
        - Output: filteredDT
        - Filter Mode: Keep data
            - [Condition from configReasonsDT]
    - Write Range (OutputData)
        - Input: filteredDT

Additional Notes:

• If your configReasons comes from a text file or other format, you may need to parse the content accordingly to get it into a workable DataTable.
• If there’s a possibility of changes in your filter criteria, your workflow will automatically adjust as it reads from the configuration file.
• Error handling should be included to manage situations like missing files or empty data tables.

This overall approach will help you filter and extract the necessary information based on your specific criteria. Adjust the activities and configurations as per your needs within UiPath.

[Jeeru_venkat_Rao]

Applied in config like below:-

and in filterdata table, I applied like this

Is this correct approach?

[Anil_G (Anil Gorthi)]

@Jeeru_venkat_Rao

you can use linq

filtereddt = dt.AsEnumerable.Where(function(x) in_Config("Key").ToString.Split(",").Any(function(y) y.Trim.ToLower.Equals(x("Reason").ToString.Trim.ToLower))).CopyToDataTable

i config give as comma separated values

cheers

[naveen.s]

Yes you can use like that approach

[Jeeru_venkat_Rao]

Thank you so much for your quick response.
@naveen.s
@Anil_G

Both approachs working fine.

[Jeeru_venkat_Rao]

Hi @Anil_G

I want to implement on linq query.
Now I want to add one more filter on existing linq query.
See below screenshot:-

Earlier we were filtering for Reason column but now I need to use appoint Date as well. Now if it should filter appoint Date by previous date-1 then reason column filter will work.

Anil bro pls modify and share the query

[Jeeru_venkat_Rao]

I tried below code:
dt_OutputData.AsEnumerable().Where(Function(x) in_Config(“CardDeclineMessages”).ToString().Split(“,”).Any(Function(y) y.Trim().ToLower() = x(“Remarks”).ToString().Trim().ToLower()) AndAlso x(“Appointment Date”).ToString() = “11/14/2024”).CopyToDataTable()

But it say’s object reference not set