Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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!
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.
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.
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.
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.
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!