Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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...
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);
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.
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;
Very elegant
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.
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...
Hi Paul,
You could put the script into a subroutine and call it 10 times.
Yes that's what I'm doing, it's actually a JScript subroutine/function.