How to efficiently filter a datatable in Pega?
I have a data table that contains over 10k records. I want to efficiently remove the records matching certain conditions.
I tried a solution which involves creating a new data table and importing (with list loop) a data row array matching conditions but it takes a very long time which disqualifies it from being used in the solution.
Pegasystems Inc.
US
@KasiaM40 This is something you'll want to do in a script using C# as the slight overhead of running via automation can get amplified at that scale. You can pass your DataTable into the script as a parameter and then filter it and return a new filtered DataTable. If you need an example, I can probably create one. Your filtering would be specific to your scenario and you'd need to understand at least a little C# to filter what you need though.
https://stackoverflow.com/questions/13012585/how-i-can-filter-a-datatable
Updated: 19 Feb 2021 13:15 EST
UNIQA
PL
@ThomasSasnett If it isn't any trouble then it certainly be much easier for me to follow an example created by you.
My filtering is a bit complex - "[Status:] = 'Open' AND ([Relevant bank] Like '%Morgan%' OR [Relevant bank] Like '%UBS%') "
Pegasystems Inc.
US
@KasiaM40 @ThomasSasnett This is exactly what the TableView component is there for and it is soooo simple to use.
- Set your LookupTable as the TableProvider for the TableView
- Set the TableView filter using the syntax you provided
- Call the ApplyFilter method from the TableView
- Call the appropriate ToTable method from the TableView which will output your filtered datatable
ToTable has 4 method signatures that are useful for various tasks:
- with zero or one parameter it outputs the filtered table
- with 2 or 3 parameters you choose whether to return distinct values and which columns to return - I use this for retrieving data like you do in Excel when you filter a column - you can return all unique values in a column
This component has many other very helpful uses. Try it out.
Pegasystems Inc.
US
@jeffbadger Yes...I actually remembered that component and am using it in the demo solution. I will attach it this afternoon.
Pegasystems Inc.
US
@KasiaM40 As Jeff alluded to, the TabelView actually works for this. In the attached solution, I built your expression (although used Closed instead of Open to see records as I didn't get around to changing much of the data in the spreadsheet) and it works.
The syntax you want to use is;
Status = 'Open' AND (Bank Like '*Morgan*' OR Bank Like '*UBS*')
Use single quotes around strings and then asterisks instead of percentage signs.
Updated: 22 Feb 2021 3:27 EST
UNIQA
PL
Updated: 22 Feb 2021 10:09 EST
UNIQA
PL
@KasiaM40 I managed to solve this programatically. I created a filtered data view and exported that dv to a new data table.
Pegasystems Inc.
US
@KasiaM40 What do you mean? Using the TableView does not require user interaction. My sample solution was built off of a Windows Form as that is the easiest way to show you how things work, but you can easily trigger these steps from your own process.