Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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);
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);