topic Re: QVD file question. in QlikView

QVD file question.

Tue, 13 May 2014 23:22:31 GMT

I have a qvd file for transactions. It consists of transactions from 10 products. Within each product there are three to four different types of transactions. Each transaction has a different transaction type ID to identify It. For instance issue tranactions has a transaction type id = 1. Now with that said my transaction qvd file is over 500 million rows. I can load the tranasaction qvd with a where clause with a transaction type id = 1 to return the issue transanctions. The problem is it when the script runs it has to go line by line finding the match criteria. This is a time consuming progress. Is there any better way to setup the qvd so it can be read quicker. Any thoughts?

Re: QVD file question.

martinpedersen — Wed, 14 May 2014 13:19:24 GMT

Hi David,

To get a fast reload of a QVD-file it need to be "QVD-Optimized". In general that means you can not do any calculations in the load and all fields previous loaded into the current table must be included in the QVD-file.

But qlikview allows the use of single parameter Exists() in QVD-optimized reloads.


//Create Data Table:
Data:
Load RecNo() as TransId, Floor(Rand()*10) as TypeId autogenerate 5000000;
Store Data into data.qvd;
Drop Table Data;


//Define Type 2 Load
_Type:
Load 8 as TypeId autogenerate 1;


//Load Table from
TransTable:
Load * From data.qvd (qvd) Where Exists (TypeId);


// Or Load TransId, TypeId From data.qvd (qvd) Where Exists (TypeId);


Drop Table _Type;

The above show the creation of a Data-Table with 5.000.000 records grouped in 10 random Types (1-10).

Then I specify what type i want to load in a temp-table => In this case TypeId = 8.

Finally I Load the Data-table as QVD-Optimized only for Type 8 with the use of "Exists(TypeId)".

Read more about QVD-Optimized loads here:

Some examples of things that will cause a non-optimised load are:

- Adding new fields to the table
- Deriving new values from a field in the QVD
- Retrieving a field twice
- Most WHERE conditions
- Joining to an existing in memory table
- Loading data into a mapping table

In contrast the things you are allowed to do are:

- Rename fields
- Omit fields
- Do a simple one field WHERE EXISTS on a field returned in the record set

http://www.quickintelligence.co.uk/qlikview-optimised-qvd-loads/

Re: QVD file question.

Wed, 14 May 2014 14:28:21 GMT

Hi David,

if i understand well you have a big QVD holding everything and you want to load only Type =1

Solution 1 :

Load everything and put a list box to select value 1.

Solution 2 :

when you store qvd make it by type, so store one Type_1.qvd, Type_2.qvd, ... you can choose then which you xant to load

No more ideas

Chris

Re: QVD file question.

Anonymous — Wed, 14 May 2014 15:18:44 GMT

Try an optimsied load, assuming that your source is a QVD.

Temp:

LOAD * Inline [

type id,

]

;

Source:

LOAD

Field1,

[type id]

FROM yoursource.qvd

WHERE Exists ([type id]);

Drop table Temp;