thank you for your answer.
I cannot use the date#(date'YYYYMMDD') function, or better, I can use it, but is not effective.
By using that function, I would fist need to load all the data, interpret the dates, so that then I can filter on the two weeks.
My goal is to have the filtering directly from the source, so, in the SQL command, to have a WHERE statement, which is picking up just the dates of my interests. but yeah, problem is that those datesin SQL are numbers.
I was trying some SQL command like ADD date or so (did not save them), but they were not leading to anywhere.
So in the end, to save time (in design phase, for sure not in reload timings) I had to rely on the "easy" way, i.e. importing and then filtering from the RESIDENT table.
but if a proper way of filtering the weeks directly from SQL source, where the field is a number, woudl be much appreciated, tho I'm aware the script might result quite complex, so maybe all in all is not worth the trouble...
You write about the Date#() function that
"By using that function, I would fist need to load all the data, interpret the dates, so that then I can filter on the two weeks."
But this is not true. If you use Preceding Load, you can do it in one go:
Load * Where week(NewDate) = 2 ;
Date#(Date,'YYYYMMDD') as NewDate;
SQL SELECT ... FROM ;
You may try creating two variables, the first for your earliest date, and a second for the latest date, then refer to those variables directly in your SELECT statement, as such:
LET v_MinDate = '20130115';
LET v_MaxDate = '20130130';
WHERE date_field >= $(v_MinDate) and date_file <= $(v_MaxDate);