Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm really struggling with date comparisons in a loading scripts.
I am loading a text file which has a date field in YYYY-MM-DD HH:MM:SS format, on loading i convert it to a date field and change the layout to DD/MM/YYYY. I then create a date variable to use for comparison.
a simplified version of my code which demonstrates my issue:
Import.txt file:
ID|Date
1|1992-01-19 00:00:00
2|1996-01-19 00:00:00
3|1998-01-19 00:00:00
4|1996-03-19 00:00:00
5|1999-07-19 00:00:00
INPUT:
LOAD
ID,
Date(Date#("Date", 'YYYY-MM-DD'), 'DD/MM/YYYY') as Date
FROM [lib://Import.txt]
(txt, codepage is 28591, embedded labels, delimiter is '|', msq);
Let vInitalReportDate = Date(Date#('31/08/2014', 'DD/MM/YYYY'), 'DD/MM/YYYY');
OUTPUT:
NoConcatenate
LOAD *
Resident INPUT
WHERE Date <= $(vInitalReportDate);
Every date in the file is less that the comparison date variable but no rows are loaded into OUTPUT. What am i doing wrong, it seems such a simple thing?
INPUT:
LOAD
ID,
Date("Date",'YYYYMMDD') as Date
FROM [lib://Import.txt]
(txt, codepage is 28591, embedded labels, delimiter is '|', msq);
Let vInitalReportDate = Date(Date#('31/08/2014', 'DD/MM/YYYY'), 'YYYYMMDD');
OUTPUT:
NoConcatenate
LOAD *
Resident INPUT
WHERE Date <= $(vInitalReportDate);
DROP TABLE INPUT;
Try changing your WHERE clause by enclosing $(vInitalReportDate) in single quotes
WHERE Date <= '$(vInitalReportDate)';
Try like:
INPUT:
LOAD
ID,
Date(Date#("Date", 'YYYY-MM-DD hh:mm:ss'), 'DD/MM/YYYY') as Date
FROM [lib://Import.txt]
(txt, codepage is 28591, embedded labels, delimiter is '|', msq)
Where Date#("Date", 'YYYY-MM-DD hh:mm:ss') <= MakeDate(2014, 8,31);