Counting the largest number of multi-values in X no. of cols

ray.wurlod · Post by **ray.wurlod** » Thu Mar 19, 2009 3:30 pm

Does it have to be 20 columns, or can you get them as one column? If not, assemble a field-mark delimited dynamic array containing the 20 columns. Apply the Counts() function to return an equivalent dynamic array of the number of value marks in each field (you may need to convert to a non-delimiter character for this). Apply the Maximum() function to get the highest count. Use Locate to find the first occurrence of this in the counts dynamic array. Create a business rule about how to handle more than one field having the same maximum count.

ray.wurlod · Post by **ray.wurlod** » Thu Mar 19, 2009 3:31 pm

Does it have to be 20 columns, or can you get them as one column? If not, assemble a field-mark delimited dynamic array containing the 20 columns. Apply the Counts() function to return an equivalent dynamic array of the number of value marks in each field (you may need to convert to a non-delimiter character for this). Apply the Maximum() function to get the highest count. Use Locate to find the first occurrence of this in the counts dynamic array. Create a business rule about how to handle more than one field having the same maximum count.

rachit82 · Post by **rachit82** » Thu Mar 19, 2009 3:43 pm

ray.wurlod wrote:Does it have to be 20 columns, or can you get them as one column? If not, assemble a field-mark delimited dynamic array containing the 20 columns. Apply the Counts() function to return an equivalent dynamic array of the number of value marks in each field (you may need to convert to a non-delimiter character for this). Apply the Maximum() function to get the highest count. Use Locate to find the first occurrence of this in the counts dynamic array. Create a business rule about how to handle more than one field having the same maximum count.

What i meant was a variable number of columns....

so you mean to say Count(col1:col2:col3:...:20,@FM)

or is it better to get maximum(col1:col2:col3....col20)?

Thanks

ray.wurlod · Post by **ray.wurlod** » Thu Mar 19, 2009 5:42 pm

If you want the maximum count, no.

Figure out your actual requirement and deploy the appropriate functions.
I've suggested the most likely.

rachit82 · Post by **rachit82** » Wed Apr 08, 2009 8:00 am

ray.wurlod wrote:If you want the maximum count, no.

Figure out your actual requirement and deploy the appropriate functions.
I've suggested the most likely.

The following is the result that i deployed as a server routine:

Function TestCompare<Arg1>

MaxCount = 0
PosOfMax = 0
NArgs = dcount<Arg1,"|">
For i = to Nargs
ThisArg = Field(Arg1,"|",i)
ThisCount = DCOUNT(ThisArg, @VM)
if ThisCount > MaxCount then
MaxCount = ThisCount
PosOfMax = i
End
Next i
Ans = Field(Arg1,"|",PosOfMax)

ray.wurlod · Post by **ray.wurlod** » Wed Apr 08, 2009 12:27 pm

You've just reinvented the MAXIMUM() function.

rachit82 · Post by **rachit82** » Wed Apr 08, 2009 12:36 pm

ray.wurlod wrote:You've just reinvented the MAXIMUM() function.

I needed the dynamic array as result which is the largest among all the columns that i am comparing.

ray.wurlod · Post by **ray.wurlod** » Wed Apr 08, 2009 12:55 pm

Yes, that's what the Maximum() function does.