4 Replies Latest reply: Mar 8, 2016 11:52 AM by Stefan Wühl RSS

    Does order by not work in the load script?

    lakwinder singh

      Hi Guys,

       

      Does order by not work in the load script? If so what do I use?

        • Re: Does order by not work in the load script?
          Sunny Talwar

          It only works in resident load. So a load from excel or any other external source cannot be ordered. You will need to create a resident table to use order by statement.

          • Re: Does order by not work in the load script?
            qlik777 view777

            Hi ,

             

            Order by will work in Load script but not in Direct load it will work in only resident load

             

            t1:

            load

            name,

            age

            from .....xls;

             

            t2:

            load

            name,age,

            1 as tmp

            resident t1

            order by name asc;

             

            drop table t1;

            • Re: Does order by not work in the load script?
              Andrew Whitfield

              Hi lak,

               

              from the Qlikview Help File:

               

              order_by   order by  is a clause used for sorting the records of a resident table before they are processed by the load statement. The resident table can be sorted by one or more fields in ascending or descending order. The sorting is made primarily by numeric value and secondarily by national collation order. This clause may only be used when the data source is a resident table.

              The ordering fields specify which field the resident table is sorted by. The field can be specified by its name or by its number in the resident table (the first field is number 1).

              orderbyfieldlist ::= fieldname [ sortorder ] { , fieldname [ sortorder ] }

              sortorder is either asc for ascending or desc for descending. If no  sortorder is specified, asc is assumed.

              fieldname, path, filename and aliasname are text strings representing what the respective names imply. Any field in the source table can be used as fieldname. However, fields created through the as clause (aliasname) are out of scope and cannot be used inside the same load statement.

               

               

              HTH

               

              Andy

              • Re: Does order by not work in the load script?
                Stefan Wühl

                Could you elaborate what the exact issue is?

                Did you get an error in the script?

                Or have you expected something else when loading the data?

                 

                As already stated, ORDER BY in a LOAD statement can be used with RESIDENT tables.

                 

                If your source itselfs supports an order by (most common example: a SELECT ... FROM ... ORDER BY....;) you can use this as well (note that the sorting is performed by the source DBMS, not QV).

                 

                Also note, that QV's ORDER BY is sorting the input table, not the ouput table (so won't result in a sorted table e.g. when using a LOAD DISTINCT).