Loading top 3 excel sheets from folder?

[Anonymous]

Hi

i have folder path is like   d:\qlikview\source

in this i have the files like

sales_01_01_2016.xlsx

sales_01_02_2016.xlsx

sales_01_03_2016.xlsx

sales_01_04_2016.xlsx

sales_01_06_2016.xlsx

sales_01_09_2016.xlsx

sales_01_10_2016.xlsx

i have excel like this from this folder i want load only top 3 excels it means latest TOP 3 DATES excel

in above example i want load only  01_10_2016,01_09_2016,01_06_2016  xl files only

how to write automation code for this

tried lot but no idea?

[swuehl · Accepted solution]

Here is a general approach:

1) Use

FOR EACH vFile IN FILELIST(..)

LOAD

     '$(vFile)' as FileName,

     Date#(Mid('$(vFile)',7,10),'MM_DD_YYYY') as Date

Autogenerate 1;

NEXT vFile

to create a table with all file names and file dates.

2) Then sort your table by Date desc and PEEK() the top 3 FileName values into variables.

3) Use another FOR EACH vFile IN FILELIST(..)

to load the top 3 files.

Look into Help for explanations and sample code for all mentioned functions.

Hope this helps,

Stefan

[Anonymous]

Thanks for reply

i tried in the same way but i did not get it

set vpath='D:\SOURCE\Newfolder';

for each vb in FileList('D:\SOURCE\Newfolder\Transact*.xlsx');

T1:

LOAD Distinct [Transaction ID],

     [Order Date],

     [Salesman ID],

     [Product ID],

     [Serial No],

     [ID Customer],

     '$(vb)' as source,

     [List Price], RowNo() as row,

     FileBaseName() AS NAME,

     DATE(date#(right(FileBaseName(),10),'MM-DD-YYYY'),'MM/DD/YYYY') as PERIOD,

     Sales,

     [Gross Margin]

FROM

$(vb)

(ooxml, embedded labels, table is Transact);

next vb

T2:

LOAD Distinct *,

1 as a

Resident T1

Order by PERIOD desc;

DROP Table T1;

LET VPEEK0=Peek('PERIOD',-5,'T2');

LET VPEEK1=Peek('PERIOD',-1,'T2');

LET VPEEK2=Peek('PERIOD',-2,'T2');

LET VPEEK3=Peek('PERIOD',-3,'T2');

LET VPEEK4=Peek('PERIOD',-4,'T2');

here it self all the peek variables are giving same date

i felt that here is the issue

pls advice

[Anonymous]

First you load the one file

after that you can write the above the load file

for each sheetname 'jan$' , 'feb$','mar$'

fields name

path

and at the path at the name you give the name $(sheetname ) than you can load the file load the want you want the files that files are come

[Anonymous]

First you load the one file

after that you can write the above the load file

for each sheetname 'jan$' , 'feb$','mar$'

fields name

path

and at the path at the name you give the name $(sheetname ) than you can load the file load the want you want the files that files are come

[swuehl]

Have a look how I generated 1 record per file name using autogerate while you are generating as many records as the input tables show. I load the table records after filtering  the dates.