Notes view - find the most frequent value

HCL Domino Domino Forum
1

Hello, all! I have a programming problem I’d like some help with.

For a given value in field X, I need to find the most frequently occurring associated value in field Y.

Example: The key value in the new doc in field X is “ABCDEF”. There are 20 documents that match the key value in field X. Seven have an associated value in field Y of “Q”, five have the value “B”, four have the value “F”, two have the value “P” and one each have the values “L” and “R”.

In this case I would like a way to do a lookup on these documents using the key value “ABCDEF” and get back the value “Q” because it is the most frequent.

I’d prefer to do this with a view and lookups rather than LS code for performance reasons. I can write the LotusScript; it’s the formula and view design that is tying me in knots.

A couple more things: This is a legacy client application. XPages are not an option, nor is a browser-based solution - this has to work with existing Notes forms. All keys and values are text, and are pre-defined in existing lists for lookup purposes.

Any suggestions are welcome. Thanks!

Keith

2

Subject: find the most frequently occurring value among lookup results

t := @DbLookup(“”; “”; “CatByRedCode”; 2); REM {column 2 presumed sorted};curVal := t[1];

curCt := 1;

maxCt := 0;

maxVal := “”;

@For(i := 2; i <= @Elements(t); i := i + 1;

@If(t[i] = curVal;

  curCt := curCt + 1;

curCt > maxCt;

  @Do(

     maxVal := curVal;

     maxCt := curCt;

     curVal := t[i];

     curCt := 1

  );

  ""

)

);

@If(maxCt < curCT; curVal; maxVal)

3

Subject: most frequent value…

With all the function code you’d have to write it would probably work better to code this using script. Calling the script agent from something like Postrecalc, set up and build a dynamic array and bucket the returned values using idx = ArrayGetIndex() then grab the largest value by walking that array. There may be a way to do this in function language that isn’t a total mess but I can’t think of a way off the top of my head. It will be just as fast and way easier to read/modify if needed.