9 Replies Latest reply: Dec 9, 2015 4:58 PM by Paul Dillon RSS

    Avg of top 3 fields in load script?

    Paul Dillon

      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?

        • Re: Avg of top 3 fields in load script?
          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

          • Re: Avg of top 3 fields in load script?
            Stefan Wühl

            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?
              Andrew Walker

              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?
                Paul Dillon

                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?
                    Paul Dillon

                    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?
                      Andrew Walker

                      Hi Paul,

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