Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

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

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

1 Solution

Accepted Solutions
elena_isupova
New Contributor III

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

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

3 Replies
elena_isupova
New Contributor III

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

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
Esteemed Contributor

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

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

Not applicable

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

Thank you Elena and Andy!! Very much appreciated!

Cheers,

Justin