Is it possible to Sort a QVD file that stored (or when loading from a QVD file)

[MEJ]

I store files into CSV format. However, I am not sure in what order it is sorting the data. 

Anyway, I would like to sort the file. I am storing the file as below, and I have no clue the way it sorts. I would like to have at least a few fields that I sort on, so that it makes at least a little sent, instead of being all randomly sorted.

Store Revrec1 into \\MMX111VSWSQV1\MappingF\Rev_BL1.qvd;

Or is it possible to sort it when reading from the QVD file (Rev_BL1.qvd)?

[petter]

You can't sort in the same load statement where you read the QVD-file. However you can do a following resident load of the table you have read and then specify an ORDER BY clause to get it sorted like you want. 

D1:
LOAD * FROM myqvd (qvd);

D2:
LOAD * RESIDENT D1 ORDER BY <field1>,<field2> .... ;

DROP TABLE D1;

If you store D2 it should be stored with the sort you have done in your  last load statement.

[MEJ]

Thank you. I have been trying the solution, but I ended up with double records. Will keep trying until I get it correct.

Seems like I am missing a DROP table somewhere (after table TEST_Resident3, but the script fails then).

TEST_Resident1:
LOAD *
FROM [C:\Users\mathias.johnson\Desktop\Test resident.xlsx] (ooxml, embedded labels, table is Data);

Store TEST_Resident1 into [C:\Users\mathias.johnson\Desktop\Test_Resident_X.qvd];

Drop Table TEST_Resident1;

//------------------------Re-sort QVD-file------------------------\\
TEST_Resident2:

LOAD *
FROM [C:\Users\mathias.johnson\Desktop\Test_Resident_X.qvd] (qvd);

TEST_Resident3:
LOAD * Resident TEST_Resident2 order by Key, [Invoice Type], [Supplier Name], [Invoice Date];

Store TEST_Resident2 into [C:\Users\mathias.johnson\Desktop\Test_Resident_Y.csv](txt);

[MEJ]

Here is an example of a test version, but I don't seem to get i right.

Anyone that knows what I am doing wrong?

Thanks

[petter]

It seems to be sorted - at least according to what QlikView/Qlik Sense regards as sorted. 

Can you explain what it is that is not sorted? What is not right?

[MEJ]

First of all, it is not sorted. I think the output is not according to table TEST_Resident3

It seems to take only TEST_Resident2

This QV application does not work though.

You would probably have to change the location where it is stored to your own desktop (or where ever).

[petter]

What is not sorted which specific field or column is not sorted? Give us a very specific example from your data. What is wrong and what do you expect?

[MEJ]

First of all, the QV load fails as per the screen dump, so something is wrong in the script.

In the CSV file, I would like the file to be sorted by Supplier Name, and after that Invoice Number.

[petter]

The failure that you have a screendump of is not due to sort. It is due to having two tables with the identical field names. In such a case Qlik will perform an auto concatenation which means that the first table will silently receive all the rows/records from the second table and the second table will not be created. That is why Qlik can't find the second table when you later in the load script refers to it.

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/LoadData/concat...

[petter]

The supplier name and invoice number IS sorted according to how QlikView (and Qlik Sense) sorts.

That is obviously and quite naturally not what you expect and this is why you claim that it is not sorted. 

Qlik does not use alphabetical sorting - it use an extended version of "Natural Sorting" - which is different.

https://en.wikipedia.org/wiki/Natural_sort_order

There are remedies for this - but it is different depending of whether you need to fix it in the UI in the app or in the load script. As long as it can be fixed in the UI I don't see the need for fixing it in the load script except in cases where you have large datasets.