Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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;
Thank you, this worked. 👍
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?