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

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