Only load certain date range

[martin_hamilton]

Hi

Is it possible to only load data which meets a date criteria when loading csv/xls tables?

I only want to load dates within the past 12 months or last 4 quarters. I dont need anymore data than that but the data being provided is incremented each time it comes from the backend systems and I have no other ability to filter out 'older' data.

I wasnt sure whether I could load the data and then drop data which didnt meet a criteria i.e. was outside the date range.

Many Thanks

Martin

[sunny_talwar]

May be like this:

Table:

LOAD ....

FROM .....

Where DateField >= AddYears(Today(), -1);

or

Table:

LOAD ....

FROM .....

Where DateField >= AddYears(MonthStart(Today()), -1);

[martinpohl]

Yes, it is possible.

Limit your data load with a where statement

load

your fields

from yourfile

where date >= date(monthstart(addmonths(today(),-11));

This example will only load datas beginning from

today                              =today()

minus 11 months           = addmonths, Parameter -11

first day of the month     = monthstart

so for today from 09/01/2016

Regards

[martin_hamilton]

When I insert the Where Statement the load throws an error stating UNKNOWN STATEMENT:

My script looks like the following:

LOAD

     ID_Number,

     Invoice_Number,

     Date (DateField, 'DD/MM/YYYY') as NewDateField

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Where [DateField], 'DD/MM/YYYY'] >= AddYears(Today(), -1);

I have also tried the Where statement the following way:

Where [DateField]>=AddYears(Today(), -1);

Still no luck.

Thanks Martin

[sunny_talwar · Accepted solution]

You have an extra semi colon. Remove this:

LOAD

    ID_Number,

    Invoice_Number,

    Date (DateField, 'DD/MM/YYYY') as NewDateField

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Where [DateField], 'DD/MM/YYYY'] >= AddYears(Today(), -1);

Try this...

LOAD

    ID_Number,

    Invoice_Number,

    Date (DateField, 'DD/MM/YYYY') as NewDateField

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq)

Where [DateField], 'DD/MM/YYYY'] >= AddYears(Today(), -1);