Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am joining two tables using "left join load…" which works good.
I then want to filter out certain rows that are old using a "where" statement but
I keep getting different results using the following code:
where date_t >= Date#(Today(), 'yyyymmdd') OR date_t = ''
and
where date_t >= '20110208' OR date_t =''
…why is this? I don't understand.
Shouldn't I get the same results? Doesn't today() actually mean today?
Using today() I also get rows with dates from January (30th, 23rd, 9th…)..I only expect to get rows from Feb 8th and later (which I do get if I enter the real date).
My tables look like this:
EMP:
LOAD
@2 as id_nr,
@4 as date_f,
@5 as date_t
..
FROM
emp.txt(txt, codepage is 1252, no labels, delimiter is '|', msq, header is 8 lines)where @5>='20110208' OR @5='';
;
//where @5 >= Date#(Today(1), 'yyyymmdd') OR @5 = '';
//where @5>='20110208' OR @5='';
LEFT JOIN LOAD
@2 as id_nr,
@3 as last_name,
@4 as first_name
..
FROM
pers.txt(txt, codepage is 1252, no labels, delimiter is '|', msq, header is 8 lines);
I really want to avoid having to "hard code" a date in my script.
Any help appreciated.[:D]
Hello Johan,
That WHERE statement is correct and it should work if the function Date() is evaluated properly. The following code works:
RawData:LOAD Date(Date('01/01/2010') + Rand() * 730, 'YYYYMMDD') AS DateAUTOGENERATE 2000; // Just dummy data to compare with ValidData:LOAD *, RecNo() AS Dummy // Another field to not concatenate with previous tableRESIDENT RawDataWHERE Date < Date(Today(), 'YYYYMMDD'); // Here the date is being evaluated correctly DROP TABLE RawData;
In the example above, "Date" field as an actual date field (so numeric) and that allows not to quote the function. So to properly evaluate the date when it's a string and passed as valid value for a field, it would be
LET vToday = Date(Today(), 'YYYYMMDD'); RawData:LOAD Text(Date(Date('01/01/2010') + Rand() * 730, 'YYYYMMDD')) AS DateAUTOGENERATE 2000; ValidData:LOAD *, RecNo() AS DummyRESIDENT RawDataWHERE Date < '$(vToday)'; DROP TABLE RawData;
Hope that helps