topic Re: Limit Data Loading based on most recent date in QlikView

Limit Data Loading based on most recent date

clstuart — Wed, 11 Jan 2023 20:55:14 GMT

I am attempting to limit the data loaded by a particular table. My limitation is that I only wish to pull the data starting 3 years prior to the most recent date from the table.

I am currently trying to use the Where statement upon loading the table, it looks like this

Where year(myDate) >= year(Max(myDate))-3;

This returns "aggregation functions are not allowed here".

Before I used a "let" statement to load the data from 3 years prior to today(), but I'm not sure how to scope it.

Where can I place a "let startYear = year(Max(myDate))-3;" so the myDate will be in scope and startYear will also be in scope for the Where?

Re: Limit Data Loading based on most recent date

maxgro — Wed, 11 Jan 2023 21:31:20 GMT

If the table with the dates is A
A:
load
DATE(MAKEDATE(2020) + FLOOR(rand()*4000)) as Date
AutoGenerate 10000;

Set a variable with the max year from A

M:
LOAD YEAR(MAX(Date))-3 as MaxYear Resident A;

LET vStartYear = PEEK('MaxYear');

Filter the table A with the variable

AA:
NOCONCATENATE LOAD * RESIDENT A WHERE Year(Date) >= $(vStartYear);

DROP TABLE A;

Re: Limit Data Loading based on most recent date

clstuart — Mon, 16 Jan 2023 12:18:14 GMT

Thank you, this worked. 👍

Re: Limit Data Loading based on most recent date

clstuart — Wed, 18 Jan 2023 16:01:13 GMT

Hello Maxgro.

One question about this solution. After implementation I noticed that the key fields for rows with dates outside of the date range are still populated.

So for example a row that originally had a date outside the cutoff range no longer has any information in it except for the ID numbers. How do I get rid of those?