1 Reply Latest reply: Feb 6, 2018 9:39 AM by Sunny Talwar RSS

    Custom order by

    Jonathan Alm

      Hi,

      I need to be able to sort an object via load order.

      The dimension is week number that today is loaded in the order 1-52.

      I need this to be loaded to start with week 33 and going around to week 32; eg:

      33

      34

      35

      ...>

      51

      1

      2

      3

      ...>

      32

       

      I have been able to solve this in the QlikView-application via the following SORT-expression in the GUI:

      Mod(AbsenceWeek-31, 52)+1

      but it does not work with the Senseobject that I am using. However the Sense object seems to accept load order.

       

      I know that I can use an inline table starting with 33 but I was hoping for a way to do this without having a table that of 52 rows.

       

      It should look something like this (if it was possible)

      Load week

      Resident

      DateTable

      Order by "Mod(AbsenceWeek-31, 52)+1";

       

      Any Ideas how I can make that ordering when I load the table?

      Kind regards, Jonathan

        • Re: Custom order by
          Sunny Talwar

          I believe it should work, but I think to change the load order of a field, it needs to be created with that sort order. week was created before this load and will use the load order from it's previous table, but if you create a new field like Week_Sort, it will be sorted with the new order.

           

          Also, you cannot use a calculation in order by, so better to create a field and use that in your order by clause

           

          DateTable:

          LOAD ....,

              Mod(AbsenceWeek-31, 52)+1 as TempField

          FROM ....;

           

          Sort:

          Load week,

              week as week_sort

          Resident DateTable

          Order by TempField;