Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
suzel404
Creator
Creator

How to filter a data from qvd with optimized load?

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.

1 Solution

Accepted Solutions
bill_markham
Champion III
Champion III

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 ;

View solution in original post

5 Replies
bill_markham
Champion III
Champion III

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
Creator
Creator
Author

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 ?

bill_markham
Champion III
Champion III

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 ;

View solution in original post

maxgro
MVP
MVP

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
Creator
Creator
Author

Thanks Bill,

It works fine !