topic Re: QVD Reload Problem in QlikView

QVD Reload Problem

sarfaraz_sheikh — Tue, 20 Jan 2015 09:48:41 GMT

Dear All,

I am reloading one QVD in my App ...but after optimizing some records the timer on Script Execution process is continue to flow till u will not click on Abort and remaining records in QVD is not optimizing.

This is happening only in the case when i am giving condition in where clause in Script. Without condition the QVD is optimizing Successfully.

But i want to extract the records as per my condition/expectation. I do not know what to do in this case

Kindly help to resolve the same .

Sarfaraz

Re: QVD Reload Problem

Peter_Cammaert — Tue, 20 Jan 2015 09:57:45 GMT

You can load the QVD as-is (all of it = optimized), then copy the data you want to keep in a new resident table using the original WHERE clause and finally drop the initial QVD table. Should load faster...

Of course, if this is a big QVD you'll need sufficient RAM.

Re: QVD Reload Problem

Tue, 20 Jan 2015 09:59:00 GMT

Hi,

1st time load data completely without any condition as sometimes queries take more time to respond compare to entire load (i.e Select *) and then take resident load along with where clause. Store this residented data into QVD.

Hope this helps.

BR,

Neha

Re: QVD Reload Problem

alexandros17 — Tue, 20 Jan 2015 09:59:48 GMT

When you add any condition in the where statement of qvd it is not optimized anymore so reload is slow. The only solution is to load qvd without conditions (optimized).
Hope this helps

Re: QVD Reload Problem

sarfaraz_sheikh — Tue, 20 Jan 2015 10:03:11 GMT

Dear Peter,

can you write some example here....so it will be good to understand .

Sarfaraz

Re: QVD Reload Problem

sarfaraz_sheikh — Tue, 20 Jan 2015 10:04:17 GMT

Dear All,

Request you to please give any small example if possible.

Sarfaraz

Re: QVD Reload Problem

Peter_Cammaert — Tue, 20 Jan 2015 10:07:47 GMT

Of course. But you'll have to fill in the blanks because you didn't post your original script (that would have been better for simply cutting and pasting a possible solution)

RawQVD:

LOAD * FROM [.\MyQVD.QVD] (qvd); // Optimized LOAD

FilteredQVD:

NOCONCATENATE

LOAD *

RESIDENT RawQVD

WHERE ...; // Add your condition(s)

DROP Table RawQVD;

Re: QVD Reload Problem

Tue, 20 Jan 2015 11:10:05 GMT

You can actually use exists and still retain an optimised load, so not quite any condition. So might be able to do the load in this way to resolve

Re: QVD Reload Problem

sarfaraz_sheikh — Tue, 20 Jan 2015 11:18:37 GMT

Dear Joe,

kindly write any Example if possible as you said !!!!

sarfaraz

Re: QVD Reload Problem

Tue, 20 Jan 2015 11:32:18 GMT

What is the condition you are trying to use? What code are you doing at the moment?

Re: QVD Reload Problem

Peter_Cammaert — Tue, 20 Jan 2015 11:44:37 GMT

A single parameter Exists() will still do an optimized load. A two-parameter Exists() will force an unoptimized load.

RawQVD:

LOAD * FROM [.\MyQVD.QVD] (qvd)

WHERE Exists(YourFieldnameToCheckInThisTable); // Optimized LOAD

Re: QVD Reload Problem

sarfaraz_sheikh — Tue, 20 Jan 2015 12:26:55 GMT

Dear Joe,

the below is my condition ...how i can put in in Exists()

Resident Helios
where (((year(CREATE_DATE)*10000+month(CREATE_DATE)*100+day(CREATE_DATE))>='20130401') and ((year(CREATE_DATE)*10000+month(CREATE_DATE)*100+day(CREATE_DATE))<='20141130'));

Sarfaraz

Re: QVD Reload Problem

Tue, 20 Jan 2015 12:37:54 GMT

Hi Sarfaraz,

exists probably isn't the best solution for that then. I would suggest going with Peter's post further up the thread and during your QVD creation, reduce and take your required subset into a second QVD, which you can then just load straight in to your end application.

Hope that helps

Joe

Re: QVD Reload Problem

Peter_Cammaert — Tue, 20 Jan 2015 12:39:40 GMT

You can't. Doing more complex things than a simple Exists() will cause an unoptimized QVD LOAD.

But you can simplify your WHERE condition to this:

WHERE (CREATE_DATE >= '1/04/2003') AND

(CREATE_DATE <= '30/11/2014');

Add this clause to the two-step example in my post above. (change the strings to your local date format)

Re: QVD Reload Problem

Tue, 20 Jan 2015 12:49:50 GMT

How dates (like 'DD/MM/YYYY' or 'DD/MM/YY' or 'YYYYMMDD' etc.) are stored in CREATED_DATE field in database?

Re: QVD Reload Problem

Tue, 20 Jan 2015 12:58:52 GMT

you could do this with a simple exists Peter, but as you probably not the best thing for this example

To do it you'd need to create a new field in your QVD, to flag where the required condition was met.

Then could just do an inline load of that value, followed by a concatenate of your QVD and a simple exists, to keep optimised.

Fact:

Load * Inline [

ConditionField

True

];

Concatenate(Fact)

Load * From myqvd Where Exists(ConditionField)

Like I say, not the best for this example, ideally I would go with split daily QVD's and a looped auto concatenate load based on an input parameter

Re: QVD Reload Problem

kuba_michalik — Tue, 20 Jan 2015 13:21:42 GMT

Fuh, that's easy (not really, and not that clear either )

Disclaimer: make sure that no field "CREATE_DATE" in created in the script before those steps.

First step: load distinct values of the field you want to filter with with. This is optimized (because we use Exist instead of DISTINCT or something) so should be lightning fast:

Values:

LOAD CREATE_DATE

From yourqvd.qvd(qvd)

Where not Exists(CREATE_DATE);

Now you have a list of distinct values, hopefully less of them than records in the QVD.

Second step, load a filtered list of values. That's unoptimized, but you are filtering a single-column table with hopefully less records than in the QVD, so should be reasonably fast (hopefully, at least faster than filtering a whole pre-loaded QVD):

FilteredValues:

NoConcatenate

LOAD CREATE_DATE

Resident Values

Where (some condition);

Third, drop the original list of values:

DROP Table Values;

Fourth, actually load the QVD (finally!). The only condition is Exists(), so the load will be optimized:

Table:

LOAD *

From yourqvd.qvd(qvd)

Where Exists(CREATE_DATE);

Fifth, drop the value list as it is not needed anymore.

I'm attaching an app which demonstrates how to do it on some autogenerated data.