Dates in Where Clause

[#1]

Hello

I have simple script with 4 records. I'm trying to use dates in the where clause to filter data.

In the attached script if I hard code the date in the where clause it works but does not seem to work with a variable although the variable is returning back the same value as the hard-coded one.

Thanks for your help.

[Gysbert_Wassenaar · Accepted solution]

This won't work: Let vDate= '=DATE(Max(Date)-1)';

That sets vDate to a string. Furthermore that string can't be evaluated since it's not a correct expression. And there is no Date to get the max from yet since you haven't loaded anything. Your statement is at the top of the script when the internal database is still empty.

What you can do is create the variable at the end of the script

HowToTab:

Load * inline [

...etc...

];

Temp:

load max(Date) as MaxDate;

load FieldValue('Date',IterNo()) as Date

autogenerate(FieldValueCount('Date'));

LET vDate = peek('MaxDate');

drop table Temp;

The next time you reload vDate will have the value of the maximum Date of the previous reload.

If you want to set the variable in the UI by selecting a value in the listbox then you need to add a Set Variable action to the OnSelect trigger of the Date field.

[sivarajs]

min and max are aggregation functions, so you need to use along with load Statements

load max(Date) as Max_date resident tab;

using peek() you can retrieve field values

Let vDate=peek('Max_date');

Generally min,max the result becomes integer, you need to convert into date using date() function

[#4]

Hi Gysbert,

Thanks for answering the question.

I think the solution would work correctly with a small dataset. I have tried that on 11 million records and for obvious reasons getting a max takes a huge time.

Not sure if there any way I can apply the date filter in the where clause on the original data set rather than getting a max and applying the filter on resident load?