FILTERED TABLE

Help
1

Someone tell me the simple answer as to why this won’t filter the >=300000 I have had it in “” and not in “” and its not having any of it
image

2

It should be outside of quotes since it’s an integer.

It could be that your datatype is incorrect in the datatable. As a test, put ExtractDataTable.Rows(0).Item("WIID").GetType.ToString and see what it puts

3

I don’t mean to dodge the question, but the Filter Data Table activity was not done very well. If it’s manipulating the table, the activity should allow you to output the results to either an Array of Rows or a DataTable. And secondly, both sides of the comparison should allow for expressions.

It’s terrible that can’t perform data and string manipulation to the value before you compare it… what if a cell has extra spaces that need to be trimmed or what if you only want to compare the numeric value within a string? The activity is almost not worth using most of the time, but maybe there is a use depending on your need for it.

My thoughts toward a solution would kind of depend on your goal as to why you are filtering this data set.
A typical process will simply be something like this:

-Filter to items to be processed as an array of rows
-Loop through each row in array, process, and update item
-Output entire data set with updated items

As you can see, there was never a point where I am extracting the filtered items and breaking them off to another output results. Like for example, say you only want to process the items where a file has not be saved yet, then you can filter to the items that has not been complete, process them, update data set… Then output the results including the items that were already complete as well. But, like I said, how you process and output the data depends on your project requirements.

—My proposed solution until there is a better way to use the Filter activity.

Assign activity: filteredData = ExtractDataTable.AsEnumerable.Where(Function(r) r("Type").ToString.Trim.ToUpper = "WI4" And r("Status").ToString.Trim.ToUpper = "OPEN" And If(IsNumeric(r("WIID").ToString.Trim), CDbl(r("WIID").ToString.Trim) >= 300000, False) ).CopyToDataTable

And replace .CopyToDataTable to .ToArray to process the filtered items while keeping entire dataset (like I pointed out earlier)

My variable called filteredData can be of type DataTable or Array <Of DataRows> depending on if you used .CopyToDataTable or .ToArray

There are also ways to do this with different syntax using a variant of .Select(), but I prefer the method I used.

If you still want to use the Filter activity, then @Dave brings up a good point that maybe your column is not of type Double or Integer. To be honest, I’m not sure how you would change that, since you read Excel back as strings usually.

Regards.

4

Very well put. I’ve personally neverused the filtered activity and either use datatable.select or the WHERE function. Clayton wrote. I find it just as easy to understand and implement and you can choose to make it as complicated or simple as you’d like

5

What is the difference between this function and dt.select(“[columname]=xxx”).copytodatatable? Just curious

6

Hey @yannip
From my understanding, they do exactly the same thing, as you can return the results back as either a table or an array of rows. So, the syntax is different.

I just find the .Select() to be more difficult to remember syntax and manipulate the comparisons for your needs.

Using .Where(Function(x) condition ), keeps all your code simple and versatile. For example:

.Where(Function(x) Convert.ToDate(x) = Now )

So it uses you basic vb. Converting your comparison to a Date with .Select() is a little different, which I would have to look it up if I needed to provide the solution on it, lol

Hope that explains the difference.
It really just comes down to preference, I guess.

7

Oh also,
We were comparing data table manipulation.

The .Where() is used on all lists, arrays, and enumerables, so the data table needs to converted to an enumerable (like dt1.AsEnumerable.Where()
whereas, with Select, it’s a built-in with Data Tables (like dt1.Select()

Additionally, there is also the .Select() that is used with lists, arrays, and enumerables, which acts as a way to change (or select) certain values and create a new list of those items.

So, .Where() can be used with arrays as well, in other words. In fact, I use this method with Arrays a ton.

So, like you were to have a list or array, then pressed period, you will see all the things that can be used with the dt1.AsEnumerable or Lists/Arrays, and they all pretty much get used similarly.

Here is an example:
You want to see if “part” of a value is contained in an array.
You can do this:

{"abcd","efgh","ijkl"}.Any(Function(x) x.Contains("bc") )

The result will be TRUE, because “bc” is contained in atleast one of the items in the array; there is also one to check a condition that is True for all items as well (I think it’s maybe .All() or something)

I hope this extra bit adds to it.

Regards.

8

Thanks for the clarification :)! I’m getting the point, I might start to use it this way as well once I get familiar with it. I only had one more question. You use function r and function x. Is there a difference or is it just your naming convention?

9

Function(x) is basically declaring a variable that can only be used within the scope between the parentheses. ‘x’ can be anything to describe how the variable is being used. So typically, if I’m working with generic values, I’ll use ‘x’, or if I’m using Data Rows, I’ll use ‘row’ or ‘r’, but you don’t want to conflict with an existing variable name like it ‘row’ is used in a ForEach then you would want to use something else. You can also be more descriptive if you want.

Just try to use a character or string that identifies what is being queried. Like a list of attachments in an email, use ‘a’ or ‘att’ or ‘attch’ or even more descriptive. And, like I said, don’t use any variable names that are also used within your workflow or there will be a conflict.

You can also use the same variable in the same line of code, as long as the parentheses close the scope for each variable.
For example, {"1","2","3"}.OrderByDescending(Function(x) CInt(x) ).Select(Function(x) CInt(x)).ToArray
As you can see I used ‘x’ twice which was only possible since the parentheses closed the scope they were declared in.