Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

bhavvibudagam
Contributor II

How to Load only Last 9 Days Data

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.

Date.png

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: How to Load only Last 9 Days Data

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);

1 Reply
MVP
MVP

Re: How to Load only Last 9 Days Data

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);

Community Browser