topic Re: Only load certain date range in QlikView

Only load certain date range

martin_hamilton — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Only load certain date range

sunny_talwar — Wed, 14 Sep 2016 14:44:22 GMT

May be like this:

Table:

LOAD ....

FROM .....

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

Table:

LOAD ....

FROM .....

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

Re: Only load certain date range

martinpohl — Wed, 14 Sep 2016 14:47:52 GMT

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

Re: Only load certain date range

martin_hamilton — Wed, 14 Sep 2016 15:01:41 GMT

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

Re: Only load certain date range

sunny_talwar — Wed, 14 Sep 2016 15:03:29 GMT

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