topic Re: Order by not working (posting the same msg.with attachment) in QlikView

Order by not working (posting the same msg.with attachment)

rahulhv1 — Fri, 05 Dec 2014 10:31:30 GMT

Hi gurus,

below is the simple code I am having

TAB1:

load NO, NAME,MARKS FROM D:\TRIAL.XLS (ooxml,embedded labels, table is Sheet1);

Noconcatenate

ABC1:

Load NO, NAME,MARKS resident TAB1 Order by NAME;

drop table TAB1;

In result I found that Order by clause do not work. I have tried different combinations i.e. NO , NUM(NO) , NAME but it doesn't work.

Pls. suggest me the solution

Re: Order by not working (posting the same msg.with attachment)

anbu1984 — Fri, 05 Dec 2014 10:37:23 GMT

If you want to order on Name in Tablebox, you can specify Ascending order in sort tab on Name

Re: Order by not working (posting the same msg.with attachment)

its_anandrjs — Fri, 05 Dec 2014 10:39:27 GMT

It is working try to click on Ctrl + T and see the difference.

1. Once load without the order table and see Ctrl + T.

2. Then add the Order table and then see Ctrl + T

You see there is order by try to load data in the table with sorted order

Or you can do by adding RecNo() as Rec

NoConcatenate

ABC1:

LOAD NO,NAME,MARKS,RecNo() as Rec Resident TAB1 Order BY NAME;

DROP TABLE TAB1;

See this two difference

Without order by

With OrderBy

See the Name order in both loads.

Regards

Anand

Re: Order by not working (posting the same msg.with attachment)

tresB — Fri, 05 Dec 2014 10:41:36 GMT

What makes you think that it is not working? Goto Table Viewer(Ctrl+T) and see the preview, it would show you the internal table view:

It is working.

Re: Order by not working (posting the same msg.with attachment)

rubenmarin — Fri, 05 Dec 2014 10:43:32 GMT

Hi Rahul, I think it's working, if you add a rowNo() to your load script you can see the order is OK:

TAB1:

LOAD NO,

NAME,

MARKS

FROM

[.\TRIAL.xlsx]

(ooxml, embedded labels, table is Sheet1);

ABC1:

NoConcatenate LOAD NO,NAME,MARKS,RowNo()as RowNumber Resident TAB1 Order BY NAME;

DROP TABLE TAB1;

In the 'Sort' tab of the object you can set how you want to sort. maybe you are sorting by loading order, it this case the loaded order for the NAME field is the order loaded in TAB1, in you want the load order to be the order of ABC1 you need to change the name of the field, ie:

AB1:

LOAD NO,

NAME as UnsortedNAME,

MARKS

FROM

[.\TRIAL.xlsx]

(ooxml, embedded labels, table is Sheet1);

ABC1:

NoConcatenate LOAD NO,UnsortedNAME as NAME,MARKS,RowNo()as RowNumber Resident TAB1 Order BY NAME;

In this case NAME has a load order from ABC1 table.

Hope I explained.

Re: Order by not working (posting the same msg.with attachment)

rahulhv1 — Fri, 05 Dec 2014 12:00:01 GMT

In preview,it is working fine, but when I put the out put as simple table box , it is not giving desire result even if IN SORT TAB, I REMOVED EVERYTHING , ALSO TRIED CHAINGING SORT BY LOAD ORDER, BUT IT DIDNT WORK.

Re: Re: Order by not working (posting the same msg.with attachment)

rubenmarin — Fri, 05 Dec 2014 12:06:08 GMT

Hi, I attacch your qvw sort alphabetical, and another table without sorting but different field, so to original sort order is the ORDER BY clause.

Re: Order by not working (posting the same msg.with attachment)

Fri, 05 Dec 2014 12:11:03 GMT

Hi Rahul, When you do a table box - it just sorts based on whatever field is first (See the 'Sort' tab of the Properties). Just promote the 'NAME' field as first one and select 'Text' as Ascending or 'Load Order' as original (since the order has already happened while loading).

Hope this solves your issue.

Re: Order by not working (posting the same msg.with attachment)

its_anandrjs — Fri, 05 Dec 2014 12:47:13 GMT

Hi rahul,

Please open single thread for the same problem it makes confusing see this thread it is same.

Re: order by not working

Try this way and make this settings in the table box remove all sort settings in the properties and use only load order after order by for all fields and also try to load like in this sequence Name,No,Marks.

TAB1:

LOAD

NAME,

NO,

MARKS

FROM

TRIAL.xlsx

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

ABC1:

LOAD NAME,NO,MARKS,RecNo() as Rec Resident TAB1 Order BY NAME;

DROP TABLE TAB1;

Regards

Anand

Re: Order by not working (posting the same msg.with attachment)

rahulhv1 — Sat, 06 Dec 2014 03:40:29 GMT

Thank you so much all of you, its working fine, only the thing is that it wasn't displaying in the table box. Thanks a lot again to all of you