Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Tyler_Waterfall
Employee
Employee

ORDER BY in resident load not ordering fields

Do you have any recommendations on either how to get the ORDER BY load statement for a RESIDENT load to work or a suitable workaround if this just doesn’t work?

Situation: Concatenating several tables with identical column names but with varying date fields ([TimeStamp]) which I am trying to display in order in a straight table by a field ([SessionHeatmapDay]) which is MMM-DD.  I cannot get either the ORDER BY in the load statement or the sort by (in the straight table) to work on either field. I am even trying to create a numeric field num(TimeStamp) as timestampSORT to ORDER BY, but that fails too. It seems to maintain the original load order of the concatenated tables.

Attached is an example w/ source QVDs.

Using 11.20 desktop

Any pointers would be helpful.

Tyler

1 Solution

Accepted Solutions
Tyler_Waterfall
Employee
Employee
Author

I’m going with the following instead which appears to work:

- removed the ORDER BY clause

- added field num(TimeStamp) as sessionHeatmapDaySort

- use this new field to sort within the chart


In other words, I abandoned the ORDER BY clause altogether.

View solution in original post

7 Replies
CELAMBARASAN
Partner - Champion
Partner - Champion

I hope you have Created the Field [SessionHeatmapDay] using Month(FieldName)& '-' & Day(FieldName)

Also add dual function to it like

Dual(Month(DateFieldName)& '-' & Day(DateFieldName), Floor(DateFieldName)) AS
SessionHeatmapDay


Then Sort by(Numeric) works.


Load Order cannot be used in your case. It is concatenated from 2 tables, which is loaded already.

Since it is loaded, sorting with load order is based on the first load not based on the order by.

Tyler_Waterfall
Employee
Employee
Author

This is helpful. The SessionHeatmapDay is generated in QlikView Expressor, but in typical fashion must be getting read into QV script as a string which it cannot sort.

I'll look into using DUAL() for the sorting.

Thanks Celambarasan!

Tyler_Waterfall
Employee
Employee
Author

I’m going with the following instead which appears to work:

- removed the ORDER BY clause

- added field num(TimeStamp) as sessionHeatmapDaySort

- use this new field to sort within the chart


In other words, I abandoned the ORDER BY clause altogether.

swuehl
MVP
MVP

Tyler,

I just had a look at your sample qvw file. Just out of curiosity (since it seems to be resolved for you), why do you think your table is not sorted?

If I just open the file, open the table viewer and look at the data, it seems to sorted ok (by timestamp desc).

It might be helpful if you add a field with rowno() or recno() to have information about your table order available in the front end.

Tyler_Waterfall
Employee
Employee
Author

You need to look at the data via a table or listbox. The SessionHetamapDay is not sorted properly (when you look at it by Sort by Load Order.)

So, maybe the issue here is that sort by Load Order does not work in connection with an ORDER BY in the load.

swuehl
MVP
MVP

I see.

The ORDER BY clause is sorting the input table in your data table load only, it is not resorting the symbol tables.

Here is an explanation of the difference between the two:

http://community.qlik.com/blogs/qlikviewdesignblog/2012/11/20/symbol-tables-and-bit-stuffed-pointers

If you really need the symbol tables ordered, too (in most cases you don't need to, especially when you can just sort by numeric value), you can do it like attached.

Tyler_Waterfall
Employee
Employee
Author

Interesting.  Thanks!

Per Henric's post that you reference, I think I've only attained NERD status --- need to keep working towards the GEEK status so I can fix these things!