Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

MEJ
New Contributor III

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

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)?

2 Solutions

Accepted Solutions
MVP
MVP

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

I have attached a modified version of you QVW that shows how it can be fixed both in the load script and in the UI.

Annotation 2019-07-17 133529.jpg

MVP
MVP

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

Please click LIKE (thumbs up) on one or more of my responses because that is the only way you can reward anyone that helps you on this forum... 🙂

I guess that all the 100 fields doesn't need to have alphabetical sort... People design QlikView applications for years without getting in to trouble or even notice that it is not alphabetical sort.

14 Replies
MVP
MVP

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

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
New Contributor III

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

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
New Contributor III

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

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

MVP
MVP

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

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
New Contributor III

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

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).

MVP
MVP

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

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
New Contributor III

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

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.

MVP
MVP

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

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...

MVP
MVP

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

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.