binary load and order by

[#1]

Hi guys,

i am doing binary load of my data model in my report qvw.

i want to do "order by" operation on the table which has come as part of binary load in my report qvw but it does not work.

Is there any relation between binary load and order by?

Binary data.qvw;

Txn:

Load * resident Tmp_Txn

where isnull(IsInvTable)

order by TxnId, TxnDate;

The Tmp_Txn has come from binary load.

the "where" clause works but 'order by" has no effect.

Am i missing anything here?

Saurabh

[rwunderlich]

You could generalize it a bit by using this script.

QUALIFY *;

data2:

NoConcatenate

LOAD * Resident data

Order BY X DESC

;

UNQUALIFY *;

DROP TABLE data;

FOR i = 1 to NoOfFields('data2')

  LET voldname = FieldName($(i), 'data2');

  LET vnewname = SubField('$(voldname)', '.',2);

  RENAME FIELD [$(voldname)] TO [$(vnewname)];

NEXT i

-Rob

[#3]

Hi Rob,

Thanks for providing scripts....

one question

Are you saying that Henric's suggestion is not working for me because of I am Using * in my load statement so I should try to rename all fields using your given script?

Saurabh

[rwunderlich]

I'm not sure because I'm still not seeing the whole picture. Are you saying the NewId and NewTxnDate fields are both not in the order you expect?

It seems to me that if you want to display your chart by TxnId and TxnDate you can just specify a numeric sort for those two dimensions in the chart sort properties.

A Load Order can sometimes be useful in charts, but not in this case because the order by is strictly numeric. Also the Load Order in charts would not be the pair on TxnId & TxnDate.

Also, perhaps a question for Henric. Does the Order By contract provide that the symbol tables will be in that order? Or just that the rows will be delivered in that input order by LOAD? Expecting a particular symbol order seems especially sketchy when two or more fields are given in the Order By.

-Rob

[#5]

Hi Rob,

Here is my problem statement....

I am doing binary load of my data model in different reports (Pivot and straight table). Each report has its own "order by" needs so I need to do "order by" in the qvw which includes the binary file.

//------

Binary data.qvw;

Txn:

Load * resident Tmp_Txn

where isnull(IsInvTable)

order by TxnId, TxnDate;

//-------------

The fields TxnId and TxnDate are in the data model loaded by the binary statement.

But they do not work because my report is not sorted by them.

Henric suggested a solution

Load TxnId as NewID, TxnDate as NewDate Resident ... Order By TxnId, TxnDate;

It is to rename those fields which are being used in "order by" .

I tried that but it is still not working.

My next question to him if It is not working because i am doing something like this (given below) because of using * in my load statement which makes my load statement look something like this

Load TxnID, TxnDate, TxnID as NewID, TxnDate as NewDate

Order by TxnID, TxnDate.

You replied to this question with a script to rename fields.

Saurabh

[CELAMBARASAN]

Hi Ramya,

I just got a doubt why you are trying to do order by clause in Script?

You have a sorting option in the charts right, you can very well use of it.

Any other specific logic/necessity that you are going to do in script based on this order?

Regards,

Celambarasan

[#7]

Hi Celambarasan,

I need to order by in load scipt because the columns can removed from the pivot chart and the user removes the column on which we want to do order by then the sorting ins gone...so i need to do sorting in load script.

Saurabh

[hic]

Rob Wunderlich: You ask "Does the Order By contract provide that the symbol tables will be in that order?"

Normally, yes. But it will not change the order of existing symbol tables. So, if the same field name is used first in a temporary table, then in the final table, you will get the symbol tables from the temporary table. Hence "wrong" sort order.

HIC

[CELAMBARASAN]

Thats not better way to deal with it.

Instead of giving a control to user to remove and add. You could give a filter to choose the field that need to shown in the chart.

Like the one QlikTech have demo file "Whats new in QlikView11"

Click here

Check the Reports tab. Hope you can provide that kind of solution, it is not that much good to make user to add and remove fields.

[#10]

Hi Henric,

Thanks for clarification...

I now am trying

Load Quantity, Txndate as Newdate, TxnID as NewID

order by Txndate, TxnId

But it is still not working.

what am i missing here?

[rwunderlich]

Ramya Saurabh

order by TxnId, TxnDate;

If that is the desired Sort, can't you just specify those fields in the chart Sort properties? I still don't understand the need for sorting in the script?

-Rob