Hi All,
How can I count distinct value in the lotus script?
I would like to write the query to generate the report in the Excel format. In the report I would like to calculate the number of “Partner”.
ie in SQL statement I can do it this way:
Select Count (Distinct Partner) from tbl
So it will return me number of partner without counting the duplicate data.
But in Lotus Notes how can I do it?
Thanks.
Subject: RE: Count Distinct value
Excel is a spreadsheet. Notes is an application development environment. There are many ways to do this sort of thing in LotusScript. Which way is best depends on what exactly you want to do.
In any case, you probably will want to start by creating a view that’s categorized by whatever value you want to count. If you add a totals column to this view (with the formula 1), you can use a NotesViewEntryCollection to read the totals lines from the category headings.
Or, if you’re only interested in one category of entry, you can use GetAllEntriesByKey method to read the collection of matching entries, and then NotesViewEntryCollection.Count to find out how many there are.
Read the Domino Designer help for examples of these methods.
Also, something to think about. In Notes, it’s not generally necessary to create reports just to show totals and averages. A Notes view can do this, so the view can serve as your report.
Subject: RE: Count Distinct value
but I need to count the number of few categories. I have 5 views with 5 different categories. Then the user need a report to count the number of categorized values from all these 5 views. So in this case, can I still do what you suggested?
Subject: RE: Count Distinct value
The easiest possible way according to me is use formula to calculate it and using the value with evaluate method.
suppose you have to calculate number of distinct values of column 1 in a view (or create a view for it)
do
a=evaluate(|@unique(@dbcolumn(“”:“”;“”:“”;“vtest”;1))|)
now a(0) will have the requisite amount