Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 ;
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
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 ?
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 ;
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
Thanks Bill,
It works fine !