Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a "date" field which contains date values in format DD/MM/YYYY.
The field also contains free format text entries.
I want to load data where my field has a date in 2014.
I am using the where ...date > '31/12/2013'.
It successfully applies the date condition, but the free format text entries are also being loaded.
Help anyone?
EDIT: The actual contents of the free format field are note being loaded, but the other table fields for those records are being loaded- I wanted to exclude those from my load
2nd EDIT - my data is in an Excel.table - I'm not sure if that is relevant to the DD/MM/YYY format I mentioned above???
3rd Edit - Contents of Free format ARE being loaded - see attached excel
this script loads 10 rows,
SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;-€ #.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';
SET DayNames='lun;mar;mer;gio;ven;sab;dom';
Directory;
LOAD [Field A],
Date,
[Memo: Loads]
FROM
[Where Date To Exclude Text.xlsx]
(ooxml, embedded labels, table is Sheet1)
where
year(Date) > 2013
;
Date | Field A | Memo: Loads |
---|---|---|
02/01/2014 | 1006 | y |
02/01/2014 | 1007 | y |
02/01/2014 | 1009 | y |
02/01/2014 | 1010 | y |
07/01/2014 | 1012 | y |
10/01/2014 | 1011 | y |
13/01/2014 | 1013 | y |
13/01/2014 | 1015 | y |
13/01/2014 | 1016 | y |
13/01/2014 | 1017 | y |
Where date > Date('31/12/2013','DD/MM/YYYY')
hi
try this
where ...date(date#(date,'DD/MM/YYYY'),'DD/MM/YYYY') > date(date#('31/12/2013','DD/MM/YYYY'),'DD/MM/YYYY')
Try this
where
date > Date(Date#(purgechar('31/12/2013','/'),'DDMMYYYY'),'DD/MM/YYYY')
Thank you - didn't work.
No effect on load.
Thank you.
Didn't work.
No data loads under this condition..
Thank you.
Didn't work.
No effect on load.
hi
try this
where
..num(.date(date#(date,'DD/MM/YYYY'),'DD/MM/YYYY') )
>
num( date(date#('31/12/2013','DD/MM/YYYY'),'DD/MM/YYYY'))
Also check condition
AND DATE<>null()
where
IsNum(date#(Field, 'DD/MM/YYYY')) and year(date#(Field, 'DD/MM/YYYY'))>2013;
this is my script to check
Source:
LOAD * INLINE [
Field
31/12/2013
31/12/2013
31/12/2012
31/12/2013
31/12/2014
31/11/2014
30/12/2013
a
31/12/2014
31/1a/2014
31/12/2014aaa
31/12/2013
as 20/10/2014
30/12/2014
31/12/2013b
];
Table:
NoConcatenate load *
resident Source
where
IsNum(date#(Field, 'DD/MM/YYYY')) and year(date#(Field, 'DD/MM/YYYY'))>2013;
DROP Table Source;