1 Reply Latest reply: Feb 17, 2018 1:48 PM by bhavani b RSS

    How to Load only Last 9 Days Data

    bhavani b

      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.

        • Re: How to Load only Last 9 Days Data
          Stefan Wühl

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