How to Load only Last 9 Days Data

[bhavvibudagam]

Hi Experts,

Can any one please help me on this.

In a folder daily with corresponding date one excel file will be updated.

From those excel file names I have extracted the date like below.

When I have run the script on tomorrow then one more date will be added. But here I have to restrict only Last 9 days data.

Please help me on how to add the last 9 days condition on below script using where condition. I have to load the data from 2/10/2018 to 2/18/2018.

Please find the below script.

LOAD

    Client,

    Score,

    Rank,

    filename() as filename,

    makedate(20&''&mid(FileName(),2,2),mid(FileName(),4,2),mid(FileName(),6,2)) as Date,

    20&''&mid(FileName(),2,2) as Year,

    mid(FileName(),4,2) as Month,

    mid(FileName(),6,2) as Day

FROM [lib://Source/A18*.xlsx]

(ooxml, embedded labels, table is Sheet1);

Thanks in advance.

[swuehl · Accepted solution]

Maybe like this (assuming Today(1) returns 2/18/2018 in your timezone)

LOAD *

WHERE Date >= Today(1)-8;

LOAD

    Client,

    Score,

    Rank,

    filename() as filename,

    makedate(20&''&mid(FileName(),2,2),mid(FileName(),4,2),mid(FileName(),6,2)) as Date,

    20&''&mid(FileName(),2,2) as Year,

    mid(FileName(),4,2) as Month,

    mid(FileName(),6,2) as Day

FROM [lib://Source/A18*.xlsx]

(ooxml, embedded labels, table is Sheet1);