Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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)
How dates (like 'DD/MM/YYYY' or 'DD/MM/YY' or 'YYYYMMDD' etc.) are stored in CREATED_DATE field in database?
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
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.