Count Unique Items in a filtered datatable column.

Ask questions about creating Graphical User Interfaces (GUI) in PowerShell and using WinForms controls.
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 1 year and 5 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
brianclark
Posts: 23
Last visit: Wed Feb 22, 2023 5:19 pm

Count Unique Items in a filtered datatable column.

Post by brianclark »

I have an application that looks up a list of IP addresses to get more information.
Within the code I gather all of the information and using that data I convert it to a datatable which I then use as the source for my datagridview.
When this data is loaded I have an existing counter that works based off of the $datagridview.rows.count.
I have filters in place that the user can use filter by City, Region, Country, or Postal and the row count updates accordingly.
What I am trying to do, however, is to show the unique counts of City, Region, and Country based on the filtering and filtered data.
Does anyone have any suggestions on how to accomplish this? The below is a snippit of the code I use when filtering, and where I need to have the counts come from after the data is loaded. Maybe I need do that differently, or somehow calculate the information differently.

Code: Select all

$tbx_IPL_FilterCity_TextChanged = {
	[string]$CityFilter = "`'%{0}%`'" -f $tbx_IPL_FilterCity.Text
	$filter = "City Like $CityFilter"
	if ($tbx_IPL_FilterCity.text -eq "")
	{
		$ipInfoTable.defaultview.rowfilter = $null
		$tbx_IPL_Count.Text = ($dgv_IPL_IPInformation.Rows.Count)
	}
	else
	{
		$ipInfoTable.defaultview.rowfilter = $filter
		$tbx_IPL_Count.Text = ($dgv_IPL_IPInformation.Rows.Count)
	}
}
In the else statement I attempted doing something like the following, however it doesn't seem to work.

Code: Select all

		
	$tbx_IPL_CityCount.Text = ($dgv_IPL_IPInformation | Sort City -Unique).Count
	$tbx_IPL_RegionCount.Text = ($dgv_IPL_IPInformation | Sort Region -Unique).Count
	$tbx_IPL_CountryCount.Text = ($dgv_IPL_IPInformation | Sort Country -Unique).Count
Any suggestion would be greatly appreciated.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Count Unique Items in a filtered datatable column.

Post by jvierra »

You can only count by the whole collection or add up the counts of each sub-collection. There is no technical or mathematical way to do this.

Perhaps you are asking the wrong question.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Count Unique Items in a filtered datatable column.

Post by jvierra »

Try this:

$tbx_IPL_CityCount.Text = ($dgv_IPL_IPInformation | select city | Where{ $_.City}).Count
User avatar
brianclark
Posts: 23
Last visit: Wed Feb 22, 2023 5:19 pm

Re: Count Unique Items in a filtered datatable column.

Post by brianclark »

So that doesn't work either.
Instead of sorting and filtering on the datagridview would it make more sense to work directly with the datatable object?

I am working right now with attempting to do counts based on the sub-collections.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Count Unique Items in a filtered datatable column.

Post by jvierra »

I thought you were working on the datetable object. After all, the filter is on the DT object.

I recommend rethinking what you need, why you need it and the objects you are working with. We cannot guess what a variable is. It is just a name. You are not giving us any clear or accurate information to work with.

It is critical that a tech that is asking for help provide clear and accurate info.

To be fair, I understand that you are not an engineer or a software developer. Desktop techs are not trained in these things, so you need to be careful to provide clear info. Code is not the best method since you are not a trained coder. Try to clarify the task you are attempting to accomplish.

Take your time and think it through.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Count Unique Items in a filtered datatable column.

Post by jvierra »

brianclark wrote: Thu Oct 13, 2022 4:12 pm I am working right now with attempting to do counts based on the sub-collections.
DGVs and DataTables do NOT have subcollections. A DT has "views". A filter filters the DT default view. A DGV displays the "default DT view". A DGV has a filtered view called "SelectedRows". The "Rows" property contains all rows availabe in the "default DT view".

You cannot count any column without counting all rows. A column is just all rows returning only the column objects. Columns are objects. Rows are objects.

What are you trying to count and why? Explain in English and do not try to explain it with code.
User avatar
brianclark
Posts: 23
Last visit: Wed Feb 22, 2023 5:19 pm

Re: Count Unique Items in a filtered datatable column.

Post by brianclark »

With the application we are loading up hundreds of IP's and running them through an application to get the location of each IP.
This image is using some test data.
Image
On first load of my test data there are 5 unique Cities, 5 Unique Regions, 2 Countries, and 6 Total Rows.
I am able to gather that information on initial load.
I need to be able to filter by Country and see that now there are 4 Unique Cities, 4 Unique Regions, 1 Country and 5 Rows of data.
I am ok if this isn't something can feasibly be done, as I can always have the user export to CSV and do any filters they need there.
This just seemed like a decent way to easily accomplish what they need.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Count Unique Items in a filtered datatable column.

Post by jvierra »

That is what I showed you before. If you want unique cities then do this. This assumes you are using a DataTable.

$tbx_IPL_CityCount.Text = ($dgv_IPL_IPInformation.DataSource | select city -unique }).Count
User avatar
brianclark
Posts: 23
Last visit: Wed Feb 22, 2023 5:19 pm

Re: Count Unique Items in a filtered datatable column.

Post by brianclark »

This is what ended up working;
$tbx_IPL_CityCount.Text = $ipInfoTable.DefaultView.ToTable($true, "City").Rows.Count

I definitely appreciate your help, if I hadn't had it I would probably still be spinning my wheels.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Count Unique Items in a filtered datatable column.

Post by jvierra »

That is exactly what I posted above except you did it the hard way.

You already have a table in the "DataSource" and it is already set to the "DefaultView". The default property for a table is the "Rows" property.

Yes, I know. The autocomplete/intellisense will not display that but it is there. You can cast the object to see it.

Your way works too. I am just showing you an easier approach which works everywhere in the Net Framework.
This topic is 1 year and 5 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked