Avg of top 3 fields in load script?

[#1]

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:

col1	col2	col3	col4	col5	col6	avg top 3 (derived)
1	2	3	4	5	6	5
9	8	7	6	5	4	8

What would my script expression be for the last column?

[sunny_talwar]

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:

LOAD *,

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

LOAD *,

  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;

LOAD *,

  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;

LOAD col1,

    col2,

    col3,

    col4,

    col5,

    col6,

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

FROM

[https://community.qlik.com/thread/194614]

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

[swuehl]

Maybe like this:

INPUT:
LOAD recno() as LineID,
  col1,
  col2,
  col3,
  col4,
  col5,
  col6
FROM
[http://community.qlik.com/thread/194614]
(html, codepage is 1252, embedded labels, table is @1);

CROSS:
CrossTable (Column, Value)
LOAD * Resident INPUT;

LEFT JOIN (INPUT)
LOAD LineID, Avg(Value) as AvgTop3
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.

[effinty2112]

Hi Paul,

This table is obtained by the load script below

col1	col2	col3	col4	col5	col6	AvgTopValues
1	2	3	4	5	6	5
9	8	7	6	5	4	8

Data:

Load  RecNo() as RecordNo,

*;

LOAD * INLINE [

    col1, col2, col3, col4, col5, col6

    1, 2, 3, 4, 5, 6

    9, 8, 7, 6, 5, 4

];

TransData:

CrossTable

LOAD *

Resident Data;

NoConcatenate

OrderedTrans:

LOAD

RecordNo,

Type,

Value

Resident TransData

Order by RecordNo, Value desc;

Drop Table TransData;

For i = 1 to FieldValueCount('RecordNo')

TopOrderedTrans:

First 3

LOAD

RecordNo,

Value

Resident OrderedTrans Where RecordNo = $(i);

Next

DROP Table OrderedTrans;

AggrTopOrderedTrans:

LOAD

RecordNo,

Avg(Value) as AvgTopValues

Resident TopOrderedTrans

Group by RecordNo;

DROP Table TopOrderedTrans;

Left Join(Data)

Load * Resident AggrTopOrderedTrans;

DROP Table AggrTopOrderedTrans;

DROP Field RecordNo;

[sunny_talwar]

Very elegant

[#6]

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.

[#7 · Accepted solution]

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...

[effinty2112]

Hi Paul,

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

[#9]

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