Solved: Re: Load * from excel files only when the excel fi... - Qlik Community

OmarBenSalem · ‎2017-12-15

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:

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?

OmarBenSalem · ‎2017-12-15

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

agigliotti · ‎2017-12-15

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

Silambarasan1306 · ‎2017-12-15

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 · ‎2017-12-15

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 · ‎2017-12-15

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

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 · ‎2017-12-15

OmarBenSalem · ‎2017-12-15

That unfortunately brought nothing:

pradosh_thakur · ‎2017-12-15

hey omar

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

Learning never stops.

OmarBenSalem · ‎2017-12-15

I altered it because of of a syntax error :

OmarBenSalem · ‎2017-12-15

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