Load only data from specific year to current

[jenmclean]

I have an AS400 ODBC connection and am working on creating new QVD's from the DB and only want to load records from 2007 to current as this is archived data and only need the last six years of data. My LOAD statement is as follows:

Vehicle:

LOAD *,

    Applymap('TirePowerLocationMap', TVHSTORE, 'M|' & TVHSTORE) as Location;

SQL SELECT *

FROM S108244C.QS36F.TMVEHC;

Store * from Vehicle into [QVD\VH\Vehicle.QVD];

Drop Table Vehicle;

VehicleDetail:

LOAD *;

SQL SELECT *

FROM S108244C.QS36F.TMVDTL;

Store * from VehicleDetail into [QVD\VH\VehicleDetail.QVD];

Drop Table VehicleDetail;

How would I write the load statement to only pull in the years I want.

Learning Qlikvew and trying to fix documents that others started.

Thanks in advance!

[Gysbert_Wassenaar · Accepted solution]

The best way is to add a where clause to the sql statement so the database only returns the records you want. Otherwise all records will be retrieved even if you then filter them in a load statement later. I don't know which of the two sql statements needs a where clause. Possibly both do.

SQL SELECT *

FROM S108244C.QS36F.TMVEHC

WHERE MY_YEAR_FIELD > 2007;

Or if you have a date field something like WHERE MY_DATE >= 2007-01-01;

You'll likely have to change 2007-01-01 to some kind of date format. Ask your local friendly database administrator if you don't know what you need to make it work.

[jenmclean]

That's what I thought, had to really dig to find exactly which field to use. Just wanted to make sure I was on the right road.