Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us March 10th, 7 ways modern analytics can help you take smarter action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bhavvibudagam
Creator II
Creator 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
swuehl
MVP
MVP

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

View solution in original post

1 Reply
swuehl
MVP
MVP

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

View solution in original post