3 Replies Latest reply: Aug 15, 2016 7:34 PM by Justin Eschauzier RSS

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

    Justin Eschauzier

      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