Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Load * from excel files only when the excel file contains today's date

Hi all,

I'm trying to load every excel file in a folder where the excel file contains today's date-1

my excels are as follow:

Capture.PNG

what I've done is the follow:

table :

let D1=  date(Today()-1,'DDMMYYYY');

FOR Each Ext in 'xlsx'

  FOR Each File in filelist ('lib://Test/'&'\*.'&Ext)

Concatenate

load  *  Where left(File,8) ='$(D1)'  ;

next File


What am I missing please?

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

I was trying to load the files relative to D-1, D-2, M-1 and Y-1 (depends on today's date) and have the last version of each file :

this worked (if someone would be interested):

For Each vPath in FileList('lib://Test/*.xlsx')

let D1= Date#(Today()-1, 'DDMMYYYY');

let D2= Date#(Today()-2, 'DDMMYYYY');

let M1=num( FirstWorkDate(AddMonths(Today()-1, -1), 1));

let Y1=num(FirstWorkDate(AddYears(Today()-1, -1), 1));

Data:

LOAD *,

Date(Date#(SubField(SubField('$(vPath)','/',4),'_',1),'DDMMYYYY')) as Date,

     Left(SubField(SubField('$(vPath)','/',4),'_',2),2) as Flag

FROM [$(vPath)]

(ooxml, embedded labels, table is Sheet1);

Next vPath

//D1

NoConcatenate

fD1:

load * where numDate = $(D1);

Load

col1,

Date,

num(Date) as numDate,

(Flag)  Resident Data ;

  versionD1:

Load num(max(Flag),'00') as maxFlagD1 Resident fD1;

let vMaxD1= Peek('maxFlagD1',-1,'versionD1');

NoConcatenate

final:

Load * Resident fD1 Where Flag= $(vMaxD1);

drop table fD1;

//D2

NoConcatenate

fD2:

load * where numDate = $(D2);

Load

col1,

Date,

num(Date) as numDate,

(Flag)  Resident Data ;

versionD2:

Load num(max(Flag),'00') as maxFlagD2 Resident fD2;

let vMaxD2= Peek('maxFlagD2',-1,'versionD2');

Concatenate(final)

  Load * Resident fD2 Where Flag= $(vMaxD2);

  drop table fD2;

//M1

NoConcatenate

fM1:

load * where numDate = $(M1);

Load

col1,

Date,

num(Date) as numDate,

(Flag)  Resident Data ;

versionM1:

Load num(max(Flag),'00') as maxFlagM1 Resident fM1;

let vMaxM1= Peek('maxFlagM1',-1,'versionM1');

Concatenate(final)

Load * Resident fM1 Where Flag= $(vMaxM1);

drop table fM1;

 

//Y1

NoConcatenate

fY1:

load * where numDate = $(Y1);

Load

col1,

Date,

num(Date) as numDate,

(Flag)  Resident Data ;

versionY1:

Load num(max(Flag),'00') as maxFlagY1 Resident fY1;

let vMaxY1= Peek('maxFlagY1',-1,'versionY1');

Concatenate(final)

  Load * Resident fY1 Where Flag= $(vMaxY1);

  drop table fY1;

   Drop Tables Data,versionD1,versionD2,versionM1,versionY1;

View solution in original post

13 Replies
Highlighted
Partner
Partner

maybe this:

table :

let D1=  date(Today()-1,'DDMMYYYY');

FOR Each File in filelist ('lib://Test/'&'\*.'&Ext)

     Concatenate

     load  *  from File Where left(File,8) ='$(D1)' ;

next File

Highlighted
Creator III
Creator III

Better you can try like this,

For Each vPath in FileList('lib://Test/*.xlsx')

Table:
LOAD

          *,
         Date(Date#(SubField(SubField('$(vPath)','/',4),'_',1),'DDMMYYYY')) as Date
FROM [$(vPath)]
(ooxml, embedded labels, table is Sheet1);

Next vPath

Highlighted
Master II
Master II

For Each vPath in FileList('lib://Test/*.xlsx')

let D1=  date(Today()-1,'DDMMYYYY');

Table:

if(wildmatch(right(filename(),8),'$(D1)') then

LOAD

          *
        
FROM [$(vPath)]
(ooxml, embedded labels, table is Sheet1);

endif

Next vPath

Learning never stops.
Highlighted
Partner
Partner

This Brought to me only the 13/12/2017 files  (why?)

Capture.PNG

Capture.PNG

while I want to have the FULL control on the files I want to import:

1) the 14/12/2017 files (today-1) and then bring only the last version 02)

and so on

Highlighted
Partner
Partner

Capture.PNG

Highlighted
Partner
Partner

That unfortunately brought nothing:

Capture.PNG

Highlighted
Master II
Master II

hey omar

you didn't try with wildmatch(). Can you try and let me know.

Learning never stops.
Highlighted
Partner
Partner

I altered it because of of a syntax error :

Capture.PNG

Highlighted
Partner
Partner

Capture.PNG