Load Data (from Excel) with Date Filter (exclude records based on date)

[#1]

Hi All,

I am trying to load some data from Excel, and want to exclude records from the previous Financial Year.

I have tried a number of things, the latest being:

[Sheet3]:

LOAD

[Dimension],

  [Week ending] as Week_ending,

  [Measure]

FROM [lib://Test/Date Load Error.xlsx]

(ooxml, embedded labels, table is Sheet3);

[Test]:

Noconcatenate

LOAD

    *

RESIDENT Sheet3

WHERE Date#(Week_ending,'DD/MM/YYYY') >= Date#('30-06-2016','DD-MM-YYYY');

DROP TABLE Sheet3;

This loads all the data in Sheet3 irrespective of the date

And I have tried:

[Sheet3]:

LOAD

[Dimension],

  [Week ending] as Week_ending,

  [Measure]

FROM [lib://Test/Date Load Error.xlsx]

(ooxml, embedded labels, table is Sheet3);

[Test]:

Noconcatenate

LOAD

    *

RESIDENT Sheet3

WHERE Date(Date#(Week_ending,'DD/MM/YYYY')) >= Date(Date#('30-06-2016','DD-MM-YYYY'));

DROP TABLE Sheet3;

This loads none of the data in Sheet3.

Attached is my sample data set.

I must be missing something very simple..

Any help would be greatly appreciated!

Thanks,

Justin

[Anonymous · Accepted solution]

Hi Justin,

I've tried this code:

SET DateFormat='D/M/YYYY';

LOAD

    [Dimension],

    [Week ending],

    [Measure]

FROM [lib://Loads/Date Load Error.xlsx]

(ooxml, embedded labels, table is Sheet3)

Where [Week ending] >= '30/06/2016';

without any resident load.

Hope it helps

Elena

[awhitfield]

Keep it simple works:

Directory;

LOAD Dimension,

     [Week ending],

     Measure

FROM

[Date Load Error.xlsx]

(ooxml, embedded labels, table is Sheet3)

Where ([Week ending]) > '30/06/2016';

HTH

Andy

[#4]

Thank you Elena and Andy!! Very much appreciated!

Cheers,

Justin