topic Re: binary load and order by in QlikView

binary load and order by

Tue, 18 Mar 2014 11:20:13 GMT

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

Re: binary load and order by

alexandros17 — Tue, 18 Mar 2014 11:28:24 GMT

It should work,

try to order in your original document (the one you load into the second) and verify if it works!

Re: binary load and order by

Tue, 18 Mar 2014 11:39:23 GMT

it works if i do order by in the original one but i can not do it there because i need to binary load this file in many different report qvws and all have different requirements.

Re: binary load and order by

hic — Tue, 18 Mar 2014 14:18:31 GMT

The Order By works fine, also with a Binary load. However, there is a trap...

If you do a

Load TxnId, TxnDate Resident ... Order By TxnId, TxnDate;

then the data table will be sorted correctly, but the symbol tables will not change: The old ones will be used. This means that "Sort by load order" in the UI will not be affected by the Order By.

But if you instead do a

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

then both data tables and symbol tables will be new and sorted according to your demand.

See more on http://community.qlik.com/blogs/qlikviewdesignblog/2012/11/20/symbol-tables-and-bit-stuffed-pointers

HIC

Re: binary load and order by

Tue, 18 Mar 2014 19:13:47 GMT

i am going to try this...

few questions...

do i need to rename all columns or just those on which i am doing order by?

so after renaming these columns will be available with new name...is it right?

Re: binary load and order by

hic — Tue, 18 Mar 2014 19:57:14 GMT

You need to rename the fields that you want re-ordered.

HIC

Re: binary load and order by

Wed, 19 Mar 2014 02:41:56 GMT

Here is my old script

Txn:

Load * resident Tmp_Txn

where isnull(IsInvTable)

order by TxnId, TxnDate;

New script

Load *, TxnId as NewID, TxnDate as NewTxnDate

where isnull(IsInvTable)

order by TxnId, TxnDate;

It is not working.

Do you think it is because of "*" used in the statement because it will bring TxnID and TxnDate as well along with NewID and NewTxnDate?

Saurabh

Re: binary load and order by

rwunderlich — Wed, 19 Mar 2014 03:03:22 GMT

Two questions.

1. Unless you specify "NoConcatenate", the

Load * resident Tmp_Txn

does not create a new table, correct?

2. Why do you want to do the order by? i.e. how will you use that order?

-Rob

Re: binary load and order by

Anonymous — Wed, 19 Mar 2014 03:04:34 GMT

you try this

Txn:

noconcatenate

Load * resident Tmp_Txn

where isnull(IsInvTable)

order by TxnId, TxnDate;

drop table Tmp_Txn;

Re: binary load and order by

Wed, 19 Mar 2014 03:07:51 GMT

I am doing NoConcatenate in my load script. It is not shown here

I need to order by because i need to show report in a certain order....in this case it is by TxnID and TxnDate

Re: binary load and order by

rwunderlich — Wed, 19 Mar 2014 03:19:35 GMT

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

Re: binary load and order by

Wed, 19 Mar 2014 03:49:04 GMT

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

Re: binary load and order by

rwunderlich — Wed, 19 Mar 2014 04:26:39 GMT

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

Re: binary load and order by

Wed, 19 Mar 2014 04:51:28 GMT

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

Re: binary load and order by

CELAMBARASAN — Wed, 19 Mar 2014 06:00:10 GMT

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

Re: binary load and order by

Wed, 19 Mar 2014 06:41:40 GMT

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

Re: binary load and order by

hic — Wed, 19 Mar 2014 06:47:49 GMT

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

Re: binary load and order by

CELAMBARASAN — Wed, 19 Mar 2014 06:51:11 GMT

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.

Re: binary load and order by

Wed, 19 Mar 2014 06:52:19 GMT

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?

Re: binary load and order by

rwunderlich — Wed, 19 Mar 2014 15:22:55 GMT

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