Restrict a data set based on date: What am I doing wrong?

[josephinetedesc]

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

[sunny_talwar]

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'

[josephinetedesc]

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

[PrashantSangle]

Hi,

Can you share your xls sheet.

Regards

[josephinetedesc]

Hi Max

I have uploaded the file ... (I think)  let me know if you cannot see it.

jo

[PrashantSangle · Accepted solution]

Hi,

Your date format is M/D/YYYY

therefore you try

[date Start Tx] >= date#('4/1/2015','M/D/YYYY')

Regards

[josephinetedesc]

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

[nagarjuna_kotha]

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' ;