topic Load order gets lost in QlikView

Load order gets lost

jarno_loubser — Wed, 17 Jul 2013 12:28:35 GMT

I have a situation where I pull one salesorder out of Oracle with SQL (select * from orders where order = 'xxx') then the data is returned in order (load order is equal to SQL result set order) . Line 1 of the sales order at the top and line 10 at the bottom. The data is larger and has a more complex key, but this should explain it.

I can also write the above resident table to QVD and read it back in and the load order is preserved.

If however I pull more data (select * from orders) - write to QVD and read back in - then filter on sales order xxx and then reduce data - my load order is lost. Line 3 might be the first row I see. Any idea why this might happen.

My work around is to add Oracle's rownum into my select to use later in the process (select *,rownum from orders), but I would prefer not to have to do this or at least understand why this is happening.

Re: Load order gets lost

luciancotea — Wed, 17 Jul 2013 12:36:49 GMT

Because reducing data moves remaining rows to new tables in memory and the original are deleted.

You should never rely on load sort. If the sort order is important to you, use rownum in query or RowNo() in QlikView load.

Re: Load order gets lost

jarno_loubser — Thu, 18 Jul 2013 11:48:09 GMT

OK - QlikView actually preserves the load order perfectly.

I found the actual issue -

My issue was that if the SQL query is not sorted specifically (order by clause), then although the sorting seams in order with a where clause in place it does not mean that all data will be returned in the order required without specifying it.

Referring to my example above the issue was resolved by a simple order by clause - "select * from orders order by order, line"

This can then be written to QVD and read in and the order remains.