Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I think what is happening is:
[date Start Tx] is working ok
BUT
DateFull - which I want because it is related to a Calendar - this is giving me ALL rows in the spreadsheet.
How do I restrict the values of DateFull? It must have something to do with being read into memory before the restriction takes place???
Jo
tableUse:
LOAD
[date Start Tx],
[date Start Tx] as DateFull,
location
FROM
[..\spreadsheet files\tableUse.xls]
(biff, embedded labels, table is Sheet1$)
WHERE
[date Start Tx] >= '01/04/2015'
// and [date Start Tx] >= 42095
AND location <> 'East'
;
Message was edited by: Josephine Tedesco I have attached the file, the date is unchanged but I have removed other columns and changed the ids Jo
Hi,
Your date format is M/D/YYYY
therefore you try
[date Start Tx] >= date#('4/1/2015','M/D/YYYY')
Regards
May be this:
tableUse:
LOAD
[date Start Tx],
[date Start Tx] as DateFull,
location
FROM
[..\spreadsheet files\tableUse.xls]
(biff, embedded labels, table is Sheet1$)
WHERE
[date Start Tx] >= Date#('01/04/2015', 'DD/MM/YYYY') //Assuming that your date is in DD/MM/YYYY format
// and [date Start Tx] >= 42095
AND location <> 'East'
Hi Sunny
thank you for your help
This ended up working
[date Start Tx]>= 01/04/2015
but the DateFull still gives me all the values in the Calendar (but not thankfully all the values in the spreadsheet) - so that is good,
Jo
Hi,
Can you share your xls sheet.
Regards
Hi Max
I have uploaded the file ... (I think) let me know if you cannot see it.
jo
Hi,
Your date format is M/D/YYYY
therefore you try
[date Start Tx] >= date#('4/1/2015','M/D/YYYY')
Regards
Thank you this works ☺
How did you know what the format was???
Also – I only see “correct answer sometimes … how should I be opening the files – is the correct sequence:
Log in – find my inbox – then hit reply (and actions such as select “correct answer” will be available?
Jo
As per your data If you just Put below code also works same:
LOAD location,
URNUM,
[date Start Tx]
FROM
(ooxml, embedded labels, table is Sheet1)Where location<>'East' ;