Limiting Load data to past 5 years

rtr13 — Tue, 27 Aug 2019 15:31:04 GMT

Hi Everyone,

Does anyone know how to limit the amount of data brought into an application? I am currently trying to limit the data to the current years data and the past 5 years. I put the following function into my calendar entity:

WHERE YearNBR >= DATEPART(YY,GETDATE())-5
AND DateDT <=GetDate()

It works for my Calendar table, but it isn't following through into any of my other tables. My other tables are using the following to limit the data:

WHERE Date < cast(getdate() as date)

Unfortunately, I wasn't the one who created the application, but I am trying to update it to only include 5 historical years of data and am unable to figure out why not all of the dates are being filtered out.

I appreciate the help!

Best,

Rose

Re: Limiting Load data to past 5 years

Miguel_Angel_Baeyens — Tue, 27 Aug 2019 16:40:23 GMT

Those are SQL functions that should be on the SQL SELECT part for each table. For that you will need to check the correct syntax for your RDBMS and driver.

You can also do it on the LOAD part of the script for each table using something like

WHERE YearNBR >= AddYears(Today(), -5)

AND DateDT <= Today();

and

WHERE Date <= Today();

For that, you need to make sure that the values in the fields "Date", "DateDT" and the value of Today() have the same format, for example, for Spain: "DD/MM/YYYY" else the comparison will fail.

topic Re: Limiting Load data to past 5 years in QlikView

Limiting Load data to past 5 years

Re: Limiting Load data to past 5 years