Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

OmarBenSalem
Esteemed Contributor

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
OmarBenSalem
Esteemed Contributor

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

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;

13 Replies
agigliotti
Honored Contributor II

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

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

silambarasan130
Contributor II

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

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

pradosh_thakur
Honored Contributor II

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

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.
OmarBenSalem
Esteemed Contributor

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

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

OmarBenSalem
Esteemed Contributor

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

Capture.PNG

OmarBenSalem
Esteemed Contributor

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

That unfortunately brought nothing:

Capture.PNG

pradosh_thakur
Honored Contributor II

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

hey omar

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

Learning never stops.
OmarBenSalem
Esteemed Contributor

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

I altered it because of of a syntax error :

Capture.PNG

OmarBenSalem
Esteemed Contributor

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

Capture.PNG

Community Browser