Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

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

8 Replies

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:

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

MVP
MVP

Re: Avg of top 3 fields in load script?

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
Honored Contributor

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:

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;

Re: Avg of top 3 fields in load script?

Very elegant

Not applicable

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.

Not applicable

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

effinty2112
Honored Contributor

Re: Avg of top 3 fields in load script?

Hi Paul,

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

Not applicable

Re: Avg of top 3 fields in load script?

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

Community Browser