9 Replies Latest reply: Dec 9, 2015 4:58 PM by Paul Dillon

# Avg of top 3 fields in load script?

I have a database with 6 numerical columns.  I need to load all 6 columns, as well as the average of the top 3 columns.  Eg: first 6 fields are from DB, last field is calculated in the load script:

col1col2col3col4col5col6avg top 3 (derived)
1234565
9876548

What would my script expression be for the last column?

• ###### Re: Avg of top 3 fields in load script?

Not the best way to do it, and may be there is a better way, but here is one way it seems to work:

Table:

RangeAvg(Max, Max2, Max3) as [Required Column];

RangeMax(If(Max = col1 or Max2 = col1, 0, col1), If(Max = col2 or Max2 = col2, 0, col2), If(Max = col3 or Max2 = col3, 0, col3),

If(Max = col4 or Max2 = col4, 0, col4), If(Max = col5 or Max2 = col5, 0, col5), If(Max = col6 or Max2 = col6, 0, col6)) as Max3;

RangeMax(If(Max = col1, 0, col1), If(Max = col2, 0, col2), If(Max = col3, 0, col3), If(Max = col4, 0, col4), If(Max = col5, 0, col5), If(Max = col6, 0, col6)) as Max2;

col2,

col3,

col4,

col5,

col6,

RangeMax(col1, col2, col3, col4, col5, col6) as Max

FROM

(html, codepage is 1252, embedded labels, table is @1);

• ###### Re: Avg of top 3 fields in load script?

Maybe like this:

```INPUT:
col1,
col2,
col3,
col4,
col5,
col6
FROM
(html, codepage is 1252, embedded labels, table is @1);

CROSS:
CrossTable (Column, Value)

LEFT JOIN (INPUT)
WHERE ValueID <=3
GROUP BY LineID;
LOAD LineID, Column, Value, AutoNumber(recno(), LineID) as ValueID
Resident CROSS
ORDER BY LineID, Value desc;

DROP TABLE CROSS;

```

edit: You can easily adapt this solution to a different TopX by changing

WHERE ValueID <=3

Seems also be more stable when there are ties, but you want to just use top 3 values.

• ###### Re: Avg of top 3 fields in load script?

Very elegant

• ###### Re: Avg of top 3 fields in load script?

Hi Paul,

This table is obtained by the load script below

col1 col2 col3 col4 col5 col6 AvgTopValues
1234565
9876548

Data:

*;

col1, col2, col3, col4, col5, col6

1, 2, 3, 4, 5, 6

9, 8, 7, 6, 5, 4

];

TransData:

CrossTable

Resident Data;

NoConcatenate

OrderedTrans:

RecordNo,

Type,

Value

Resident TransData

Order by RecordNo, Value desc;

Drop Table TransData;

For i = 1 to FieldValueCount('RecordNo')

TopOrderedTrans:

First 3

RecordNo,

Value

Resident OrderedTrans Where RecordNo = \$(i);

Next

DROP Table OrderedTrans;

AggrTopOrderedTrans:

RecordNo,

Avg(Value) as AvgTopValues

Resident TopOrderedTrans

Group by RecordNo;

DROP Table TopOrderedTrans;

Left Join(Data)

DROP Table AggrTopOrderedTrans;

DROP Field RecordNo;

• ###### Re: Avg of top 3 fields in load script?

Thanks guys for all the answers!

I actually need to do this about 10 times within a single load script, in which case the crosstable stuff will need an unweildly amount of script.  I'm currently investigating whether I can implement an extension function that I could call from the load script.

Thanks again.

• ###### Re: Avg of top 3 fields in load script?

I ended up adding this code to "Edit Module":

```function avgtop3(arguments) {
arguments.sort(numDescComparator);
switch (arguments.length) {
case 1:
return arguments[0];
case 2:
return (arguments[0] + arguments[1]) / 2;
default:
return (arguments[0] + arguments[1] + arguments[2]) / 3;
}
}
function numDescComparator(n1, n2) {
return n2 - n1;
}
// JScript 5.8 is very old and doesn't support "..." style varargs, and load scripts don't allow passing array literals, so need these wrappers:
function avgtop3of4(n1,n2,n3,n4) { return avgtop3([n1,n2,n3,n4]); }
function avgtop3of5(n1,n2,n3,n4,n5) { return avgtop3([n1,n2,n3,n4,n5]); }
function avgtop3of6(n1,n2,n3,n4,n5,n6) { return avgtop3([n1,n2,n3,n4,n5,n6]); }
function avgtop3of7(n1,n2,n3,n4,n5,n6,n7) { return avgtop3([n1,n2,n3,n4,n5,n6,n7]); } !
```

Then in my load script:  LOAD *, avgtop3(col1, col2, col3, col4) AS AvgTop3, etc...

• ###### Re: Avg of top 3 fields in load script?

Hi Paul,

You could put the script into a subroutine and call it 10 times.

• ###### Re: Avg of top 3 fields in load script?

Yes that's what I'm doing, it's actually a JScript subroutine/function.