Lesson 9 - Filter Wizard not filtering data table as expected

Learn Academy Feedback
1

Hi there,

I am trying to recreate one of the activities shown in Lesson 9 for data tables however when I run the result my filters are not applied as expected.

It is the example that has the “Employees 300” excel file and I am trying to filter by Age < 30 and Pay > 100000 but no filter is applied to my output.

Has anyone been able to use the Filter Wizard successfully?

Thanks

2

Can you please attach an image of your activity, meanwhile try to make sure that:

  • The table in the output (top right) is well set to a dataTable variable
  • The Filter rows and the output columns are well set
3

Hi,

I tried to upload XAML file but am not allowed to upload unfortunately.

Here is screenshot of the flow with variables:

image
image1100×667 50.9 KB

4

Here is the filter wizard settings:
image

5

In the Output Columns if the Keep is checked please add the columns that you want to keep, if you want to keep everything just check Remove and keep the columns empty

6

I want to keep everything so I have left the Output Columns to Remove with no columns specified - still not filtering unfortunately.

7

Okay last quick question in the output dataTable your input is DT1 right?

8

That’s correct and my output is DTstring which is the text I ask the message box to display.

9

Hi @rachelfonseca
I tried it myself and it’s working fine
Main.xaml (12.9 KB)

10

Hi,

That’s interesting the issue must lie in the excel application scope/read range then as you have used the build data table instead

These are the settings for read range:

image
image1294×504 47.8 KB

1 Like
11

Try to make an output data table and message box of the output of the read range to make sure that you have the dataTable filled with data if this step is successful then it’s the same thing as my workflow for the rest, also check that the Headers are there for DT.

12

Hi reda,

I’m facing the same issue as rachelfonseca encounted and I did more testing for filter data table acitivity and believe this may be a bug.

Here is what I tested:

  1. the filter function is working if you use any string column, like “name” column
  2. the filter function wouldn’t work nor throwing any errors if you use number column, like “age” or “pay”
  3. but sort data table activity working with either string or number column

your code is working that is because you build a table and explicitly specify the type of column is int32, so filter data table activity knows the column ‘pay’ is a number and can compare with another number, however when filter data table activity get data from a read range activity it may mistreat the column ‘pay’ as a string, so it cannot compare a string with a number (this is what i guess) and causes the filter function is not working.

2 Likes
13

Hi sgchris,

Thanks for your comments. Interesting that you can get the filter data table activity to work with string columns - I have just tried and it will not filter those either for me.

I also tried some tests on the sort data table activity but it did not sort nor did it error on any of the columns- like it cannot read my excel at all.

Hopefully we can get to the bottom of this.

Thanks

14

I am having the exact same problem with Practice 2 (there are 2 columns - “Name” (string), and “ID” (Int32, I presume)). Filtering the table by ID just won’t work - the tests always fail, so I end up with the unchanged table if I choose to discard the filtered rows, or with an empty table if I choose to keep them.

Here are the things I’ve tried:

  • Switching the PreserveFormat checkbox on/off
  • Filtering the table by the “name” field (string) - works fine
  • Sorting the table - works for both fields. However, the ID column is clearly treated as string (the order is 11, 2, 21, 38, 5, and so on).
  • Playing with the column selection mode as suggested above
  • Tweaking the tests (“(not) empty” works fine for the ID column; all integer comparison tests fail, string comparison tests, oddly enough, fail too; I was told that explicitly specifying the type with Convert.toDouble(value) would work, and it didn’t; neither did explicitly typing in the value with a decimal point).
  • Explicitly defining the data types with a “Generate Data Table” activity before reading the xls - did nothing.

At this point, I’ve run out of things to tweak. I’ve written the whole thing off as a bug and skipped the exercise.

2 Likes
15

Pretty certain I am having the same problem within Lesson 9

16

Hi all,

I raised this through the official support too and have had the following response:

The main problem was with the data format of columns of DataTable which we have to filter. In case you create a datatable using “Build DataTable” activity, you can chose column dataType as Int32 or String, hence, filtering was working flawlessly.
Hence, we have picked each cell element and converted to the required data format & saved it to another DataTable as shown in attachment. This has returned the required result. We recommend you to perform the same. Also, the excel sheet you shared had formula for changing “Age” column, so we have kept fixed values to check the result.

Please note, changing data format at excel will not help, as all values are taken by default as generic.

image
image460×720 15.9 KB

The support guys said they would get the vid/workflow updated too.

3 Likes
17

That’s… far from elegant.

3 Likes
18

Agreed! not the most flexible solution either, seems like the entire for each activity could be built into the read range

2 Likes
19

So sad, because the filter tool seemed so slick. But as long as they get the video updated that would be great, but I can’t say I would have figured that out.

1 Like
20

I have found it much simpler to invoke VBA and run code to provide any advanced filtering of the excel sheet, then use read range and select the filter option. Let me know if you need any code samples.

2 Likes