Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
Employee
Employee

Re: ORDER BY in resident load not ordering fields

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.

7 Replies

Re: ORDER BY in resident load not ordering fields

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.

Employee
Employee

Re: ORDER BY in resident load not ordering fields

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!

Employee
Employee

Re: ORDER BY in resident load not ordering fields

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.

MVP
MVP

Re: ORDER BY in resident load not ordering fields

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.

Employee
Employee

Re: ORDER BY in resident load not ordering fields

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.

MVP
MVP

Re: Re: ORDER BY in resident load not ordering fields

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.

Employee
Employee

Re: Re: ORDER BY in resident load not ordering fields

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!

Community Browser