Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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?

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

8 Replies
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);


Capture.PNG

swuehl
MVP
MVP

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

Hi Paul,

This table is obtained by the load script below

col1 col2 col3 col4 col5 col6 AvgTopValues
1234565
9876548

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

Not applicable
Author

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.

Not applicable
Author

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

Hi Paul,

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

Not applicable
Author

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