topic Re: Load Data (from Excel) with Date Filter (exclude records based on date) in App Development

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

Mon, 15 Aug 2016 06:06:38 GMT

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

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

Anonymous — Mon, 15 Aug 2016 08:23:36 GMT

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

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

awhitfield — Mon, 15 Aug 2016 09:52:48 GMT

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

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

Mon, 15 Aug 2016 23:34:26 GMT

Thank you Elena and Andy!! Very much appreciated!

Cheers,

Justin