Question
Synechron Inc.
US
Last activity: 4 Oct 2018 13:54 EDT
To Filter the data coming from Excel sheet in Pega Robotics studio
Hi,
I have a Excel sheet from which I need to fetch the account number pertaining to Account type deposit and two more filter. Kindly help me to solve this problem.
Thanks and Best Regards,
Madhukar Ganesh Chatra
**Moderation Team has archived post**
This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.
Accepted Solution
Pegasystems Inc.
US
Is the data in a tabular format? If so, you can use the Excel connector ExportData method to export the contents of a range to a data table. You can then use a variety of methods to filter the data (for instance: Pega Robotics TableView component). Filtering the data externally will be necessary unless you want to process the rows one at a time. The Excel connector does not currently support applying a filter to Excel.
Synechron Inc.
US
Hi Jeff,
Right now Line of Buisness is manually applying filters to excel sheet ( The excel sheet has customer account number info with other information and we need account number that are of deposit type, approved and those accounts which are approved on current date)
We are trying to automate this task, I mean we need to get these account numbers into datasource or grid automatically after applying the excel filters.
Thanks and Best Regards,
Madhukar Ganesh Chatra
Accepted Solution
Pegasystems Inc.
US
Is the data in a tabular format? If so, you can use the Excel connector ExportData method to export the contents of a range to a data table. You can then use a variety of methods to filter the data (for instance: Pega Robotics TableView component). Filtering the data externally will be necessary unless you want to process the rows one at a time. The Excel connector does not currently support applying a filter to Excel.
Synechron Inc.
US
Hi Jeff,
Thanks for your help and time. Kindly send me screenshot showing the usage of TableView component if you have it.
Thanks and Best Regards,
Madhukar Ganesh Chatra
Pegasystems Inc.
US
Here is the help article on the component. It really just lists the methods though and isn't a how-to. Once you have your data filtered, you can call the ToTable method to get a DataTable containing just the filtered rows. Also, depending on what you are doing, you can iterate them from the component. Assuming the rows have a unique identifier, you can also create a LookupTable with the same columns and call its ReplaceTable method to place these records into the lookTable for other uses.
http://help.openspan.com/80/Components/Data_Table_Viewer.htm
Synechron Inc.
US
Hi Jeff,
Also if am not planning to use external data table for excel data storage, other than lookup table where else I can fetch the excel data and store for temp purpose, so that from there I can connect it to table view for filtering and do the further processing.
Thanks and Best Regards,
Madhukar Ganesh Chatra
TCS
IN
Hi,
Would you please provide a snapshot, how to apply filter in TableView component. I've tried both Filter and RowFilter properties but none of them worked. I've bind TableView with Lookup Table component and want to filter the records which matches with given filter criteria. I've written the statement as below, pl correct me if this is wrong:
where Product = 'Banking'
Pegasystems Inc.
US
You do not need the word "where". Just update the Filter property of the table view to "Product='Banking" (without the quotation marks). After updating the property, you'll need to call the ApplyFilterMethod.
Royal Bank of scotland
IN
Hi.. could you please help me with a snapshot of example how to bring data from excel to a tableview and apply filter on that on two column values. One value is numeric and other is text. I have searched different topics on this forum, did some R&D but looks like missing something. Its not allowed to take and upload a screenshot from my office. Could you please help me with a simple example.
Westpac Banking Corporation
AU
PFA screenshot - reading data from excel and adding to lookuptable and then apply filter in tableview.
Two columns filter condition: Id= @p1 and OrdNo = @p2
Note: you need pass value in '' if the data type is string.
Westpac Banking Corporation
AU
No need to iterate through each row. In the attached screenshot the loop is to get excel data into lookuptable. After this loop completion then apply filter.
Pegasystems Inc.
US
You can use the ExportData to extract a range as a data table. If the column headers match the column names in your lookup table you can pull the export directly into your lookup table.
Pegasystems Inc.
US
Building a filter statement uses SQL like syntax. Whenever I have a question I refer to this site.
String comparison against a literal is done with the literal surrounded by single quotes like this:
Name = 'Jeff'
Comparing numeric values to a literal is done without quotes:
Year = 2018
To compare multiple columns you would string this together with AND or with OR:
Name = 'Jeff' and Year = 2018
With the TableView, you can apply a Filter with replaceable parameters by substituting the literal with a variable name (a variable starts with an @ sign):
Name = '@name' and Year = @year