How to filter a data from qvd with optimized load?

[suzel404]

Hi all,

I have a qvd with 27 millions of records and 55 columns.

How can I reduce my qvd with year and lastYear and Type = A,B,E ?

LET vLastYear = Year(Today());

LOAD

     CalendarYear,

     TYPE,

     ....

FROM QVD

WHERE CalendarYear >= '$(vLastYear)' AND MATCH(TYPE,A,B,E);

Or

LOAD

     *

WHERE MATCH(TYPE,A,B,E);

LOAD

     CalendarYear,

     TYPE,

     ....

FROM QVD

WHERE CalendarYear >= '$(vLastYear)';

What is the best methods to obtain a optimized qvd reload ?

Thanks for your help.

[Anonymous]

For an optimized load from qvd you can use one WHERE EXISTS.

So you load a small table first like a Calendar and do WHERE EXISTS against a field in that table.  Or do an inline load say for your A,B,E

[suzel404]

Thank Bill.

I test this.

TempSUI:

LOAD * INLINE [
Etat suivi
Relance
Support
Résolu
];



TempCAL:

LOAD * INLINE [
Year
2012
2013
];



SUI_QVD:

LOAD
  Year,
  [n° PDV],
     [n° Incident],
     [n° Suivi],
     Solution,
     [Solution complémentaire],
     [Imputation générale suivi],
     [Imputation logiciel suivi],
     [Imputation matériel suivi],
     [Matériel suivi],
     [Logiciel suivi],
     [Solution STD],
     [Type suivi],
     [Etat suivi],
     [Début suivi],
     [Fin suivi]
FROM SUI_QVD.qvd(qvd)
WHERE EXISTS(Year) AND EXISTS([Etat suivi]);

DROP TABLE TempCAL;
DROP TABLE TempSUI;

The problem, the exists function work only with one field.

Did you have a better solution ?

[Anonymous · Accepted solution]

In your QVD generator create a new field concatenating  your 2 fields together, maybe something like this :

     [Year] & [Etat suivi]     as     [KeyForWhereExists]

And adjust your InLine load, to maybe something like this ?

Temp:

LOAD * INLINE [

Etat suivi

Relance

Support

Résolu

];

outer join

Temp:

LOAD * INLINE [

Year

2012

2013

];

load

[Year] & [Etat suivi]    as [KeyForWhereExists]

Resident Temp;

drop table Temp ;

[maxgro]

with exists() and exists()

the load isn't optimized

you have to load from qvd and filter first fiels, store in qvd and load and filter the second one

or

concatenate the 2 fields (Etat Year) before storing in qvd

[suzel404]

Thanks Bill,

It works fine !