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

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

MEJ — Wed, 25 Nov 2020 16:16:04 GMT

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

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

petter — Mon, 15 Jul 2019 12:01:25 GMT

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.

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

MEJ — Tue, 16 Jul 2019 14:25:28 GMT

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

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

MEJ — Wed, 17 Jul 2019 07:44:36 GMT

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

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

petter — Wed, 17 Jul 2019 09:49:24 GMT

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?

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

MEJ — Wed, 17 Jul 2019 09:54:23 GMT

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

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

petter — Wed, 17 Jul 2019 09:56:36 GMT

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?

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

MEJ — Wed, 17 Jul 2019 10:08:08 GMT

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.

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

petter — Wed, 17 Jul 2019 10:26:45 GMT

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/concatenate-tables.htm

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

petter — Wed, 17 Jul 2019 10:59:04 GMT

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.

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

MEJ — Wed, 17 Jul 2019 11:03:34 GMT

Not quite sure what you mean by having the identical name for two tables. To me it looks like I have been using the same logic you suggested?

Thanks

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

petter — Wed, 17 Jul 2019 11:36:21 GMT

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.

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

petter — Wed, 17 Jul 2019 11:38:41 GMT

You have to read up on the link I sent you about "auto concatenation".... You can avoid it by using a prefix to the load statement called NOCONCATENATE. Anyways I posted a solution for you where I have fixed the load script issue and given a solution as to how to get QlikView to use alphabetical sort on a specific field...

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

MEJ — Wed, 17 Jul 2019 11:48:10 GMT

Thanks for your help.

Wow, that was a lot more complicated than I expected! I have approximately 100 fields in the database, so I hope it will be possible to replicate.

I will study your solution, and see if I can make it work.

Thanks again.

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

petter — Wed, 17 Jul 2019 12:01:40 GMT

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.