topic Re: How to filter a data from qvd with optimized load? in QlikView

How to filter a data from qvd with optimized load?

suzel404 — Sat, 22 Nov 2014 16:22:52 GMT

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);

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.

Re: How to filter a data from qvd with optimized load?

Anonymous — Sat, 22 Nov 2014 16:26:34 GMT

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

Re: How to filter a data from qvd with optimized load?

suzel404 — Sat, 22 Nov 2014 17:33:32 GMT

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 ?

Re: How to filter a data from qvd with optimized load?

Anonymous — Sat, 22 Nov 2014 17:50:10 GMT

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 ;

Re: How to filter a data from qvd with optimized load?

maxgro — Sat, 22 Nov 2014 17:54:06 GMT

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

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

Re: How to filter a data from qvd with optimized load?

suzel404 — Sat, 22 Nov 2014 18:57:09 GMT

Thanks Bill,

It works fine !