topic Re: EXIST while loading QVD in QlikView

EXIST while loading QVD

Mon, 03 Mar 2014 21:08:23 GMT

Hi,

i have used exist function

like this

Division:

LOAD Division,

[Division Number]

FROM QVD\Division.qvd (qvd)

Where Exists([Division Number]);

regards

boby

Re: EXIST while loading QVD

Mon, 03 Mar 2014 21:09:31 GMT

in the above scenario is it optimized load or NON Optimized load.

Regards

Mahesh

Re: EXIST while loading QVD

Peter_Cammaert — Mon, 03 Mar 2014 21:10:29 GMT

Good job.

Is there any problem with this?

Peter

Edit: Unoptimized. You're filtering QVD records, therefor QlikView cannot load the QVD en bloc, but has to evaluate every single record.

Re: EXIST while loading QVD

agilos_mla — Mon, 03 Mar 2014 21:11:02 GMT

What is your issue about?

Using the exists as this, it will only load the records for the first division number read !

EXISTS does not affect qvd optimization.

Re: EXIST while loading QVD

Mon, 03 Mar 2014 21:24:38 GMT

Thnak you i got solution from

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

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

This sounds hugely restrictive, but then most things you would want to achieve can be coded for. For example, if you need to add fields – do this in the QVD generate routine rather than when reading the QVD. Similarly, if you need to derive a value do this when you generate the QVD also. Even complex WHERE statements can be done by deriving flags or composite keys in the QVD generate routine and then doing a simple WHERE EXISTS on a temporary table (even if that temporary table is just a single row from an in-line table).

In fact, optimised QVD loads with a WHERE EXISTS clause on each subsequent load statement is a simple but effective way of quickly building documents which contain related subsets of data – but that is something for another post.

So, how do you know if your load is optimised? Well, the first way is by noticing it is still running when you return to your desk with a fresh cup of coffee. The other is by checking the load progress dialog. Optimised loads show the text qvd optimized as the data is being pulled from the QVD – in contrast no message is shown when the load is non-optimised.

http://www.quickintelligence.co.uk/wp-content/uploads/2013/02/OptimisedQVD.png

Re: EXIST while loading QVD

rwunderlich — Mon, 03 Mar 2014 21:30:19 GMT

Peter Cammaert

A single parameter exists() is an OPTIMIZED load. So this one is optimized. Two parameter exists() is un-optimized.

Michaël Laenen

I think you can assume that the set of [Division Number] was read in a an earlier LOAD.

-Rob

http://masterssummit.com

Re: EXIST while loading QVD

Peter_Cammaert — Mon, 03 Mar 2014 21:35:06 GMT

How does QT do this? Preload the entire QVD and then remove in-memory the rows it doesn't need?

Re: EXIST while loading QVD

rwunderlich — Mon, 03 Mar 2014 22:47:06 GMT

I'm not sure how it's done. But that sounds like a good theory.

-Rob