topic Re: Avg of top 3 fields in load script? in QlikView

Avg of top 3 fields in load script?

Tue, 08 Dec 2015 22:32:20 GMT

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?

Re: Avg of top 3 fields in load script?

sunny_talwar — Tue, 08 Dec 2015 23:52:05 GMT

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

Re: Avg of top 3 fields in load script?

swuehl — Wed, 09 Dec 2015 00:04:31 GMT

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.

Re: Avg of top 3 fields in load script?

effinty2112 — Wed, 09 Dec 2015 00:15:58 GMT

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

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?

sunny_talwar — Wed, 09 Dec 2015 00:24:21 GMT

Very elegant

Re: Avg of top 3 fields in load script?

Wed, 09 Dec 2015 01:18:30 GMT

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?

Wed, 09 Dec 2015 08:42:46 GMT

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?

effinty2112 — Wed, 09 Dec 2015 20:43:42 GMT

Hi Paul,

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

Re: Avg of top 3 fields in load script?

Wed, 09 Dec 2015 21:58:25 GMT

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