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

ORDER BY in resident load not ordering fields

Tyler_Waterfall — Wed, 16 Oct 2013 13:02:37 GMT

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

Re: ORDER BY in resident load not ordering fields

CELAMBARASAN — Wed, 16 Oct 2013 13:09:19 GMT

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.

Re: ORDER BY in resident load not ordering fields

Tyler_Waterfall — Wed, 16 Oct 2013 13:12:36 GMT

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!

Re: ORDER BY in resident load not ordering fields

Tyler_Waterfall — Wed, 16 Oct 2013 13:39:52 GMT

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.

Re: ORDER BY in resident load not ordering fields

swuehl — Wed, 16 Oct 2013 14:00:18 GMT

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.

Re: ORDER BY in resident load not ordering fields

Tyler_Waterfall — Wed, 16 Oct 2013 14:10:11 GMT

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.

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

swuehl — Wed, 16 Oct 2013 14:34:13 GMT

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.

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

Tyler_Waterfall — Wed, 16 Oct 2013 14:46:14 GMT

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!