topic Re: Loading top 3 excel sheets from folder? in QlikView

Loading top 3 excel sheets from folder?

Anonymous — Tue, 01 Mar 2016 19:02:34 GMT

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?

Re: Loading top 3 excel sheets from folder?

swuehl — Tue, 01 Mar 2016 19:20:03 GMT

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

Re: Loading top 3 excel sheets from folder?

Anonymous — Wed, 02 Mar 2016 05:01:35 GMT

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

Re: Loading top 3 excel sheets from folder?

Anonymous — Wed, 02 Mar 2016 05:11:40 GMT

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

Re: Loading top 3 excel sheets from folder?

Anonymous — Wed, 02 Mar 2016 05:14:00 GMT

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

Re: Loading top 3 excel sheets from folder?

swuehl — Wed, 02 Mar 2016 07:55:34 GMT

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.