Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikview979
Specialist
Specialist

First 3 Sheets Data

Hi Experts,

I have one folder  like folder name(TOP5) .this folder having  5 excel sheets  names like

2014_04_21.xlsx

2014_04_22.xlsx

2014_04_23.xlsx

2014_04_24.xslx

2014_04_25.xlsx

i need First 3 excels data

Expected output:-

2014_04_23.xlsx

2014_04_24.xslx

2014_04_25.xlsx

here i need this files data.in future if add more sheets i need only first 3;

Ex:-

2014_04_21.xlsx

2014_04_22.xlsx

2014_04_23.xlsx

-

-

-

-

-

-

-

-

2014_05_05.xslx

2014_05_06.xlsx

2014_05_07.xlsx

here i need 2014_05_05.xslx,2014_05_06.xlsx,2014_05_07.xlsx


files data.

1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

In that case, you can use below code

Set vPath = 'C:\Users\Tamil\Desktop\New folder\'; 

Files: 

LOAD  Distinct

Date(Date#(FileBaseName(),'YYYY_MM_DD'))  as FileDate, 

FilePath()                                as FileName 

FROM [$(vPath)*.xlsx]

(ooxml, embedded labels, table is Sheet1); 

 

NoConcatenate 

Latest: 

First 3 LOAD 

FileDate, 

FileName 

Resident Files order by FileDate Desc;  

 

DROP Table Files; 

 

Data:

LOAD '' as Test AutoGenerate 0; 

 

LET NumRows = NoOfRows('Latest'); 

 

FOR i=1 to $(NumRows) 

LET vFileName =FieldValue('FileName',$(i)); 

Concatenate (Data)

LOAD * 

From $(vFileName) (ooxml, embedded labels, table is Sheet1); 

NEXT 

DROP Field Test;

DROP Table Latest;

View solution in original post

9 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

Use a script like this:

For Each vFile in FileList('<filepath> \*.xlsx')

  Sheets:

  LOAD FileName() as Filename

  From [$(vFile)]

  (ooxml, embedded labels, table is Sheet1);

Next

Sheets2:

NoConcatenate

First 3 LOAD Filename

Resident Sheets

Order By Filename DESC;

For i = 0 To NoOfRows('Sheets2') - 1

  Let vFile = Peek('Filename', i, 'Sheets2');

  Results:

  LOAD *

  From [$(vFile)]

  (ooxml, embedded labels, table is Sheet1);

Next;

DROP Table Sheets;

DROP Table Sheets2;

Set the filepath to the correct path for your source files and adjust the final load (in Results) to your requirements. Note that this script expects all the Excel files to have the same set of fields as it relies on auto-concatenation.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
tamilarasu
Champion
Champion

Hi Mahesh,

Try like below,

Set vPath = 'C:\Users\Tamil\Desktop\New folder\';

Files:

LOAD  Distinct

Date(Date#(FileBaseName(),'YYYY_MM_DD'))  as FileDate,

FilePath()                                as FileName

FROM [$(vPath)*.xlsx]

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Latest:

First 3 LOAD

FileDate,

FileName

Resident Files order by FileDate Desc; 

DROP Table Files;

LET NumRows = NoOfRows('Latest');

FOR i=1 to $(NumRows)

LET vFileName =FieldValue('FileName',$(i));

LOAD *

From $(vFileName) (ooxml, embedded labels, table is Sheet1);

NEXT

DROP Table Latest;

Sample file attached for your sample data.

Kushal_Chawda

Hi Mahesh,

Make sure that Your Excel files are having same number of column with same field name in all excel, other wise above solution will not work.

tamilarasu
Champion
Champion

In that case, you can use below code

Set vPath = 'C:\Users\Tamil\Desktop\New folder\'; 

Files: 

LOAD  Distinct

Date(Date#(FileBaseName(),'YYYY_MM_DD'))  as FileDate, 

FilePath()                                as FileName 

FROM [$(vPath)*.xlsx]

(ooxml, embedded labels, table is Sheet1); 

 

NoConcatenate 

Latest: 

First 3 LOAD 

FileDate, 

FileName 

Resident Files order by FileDate Desc;  

 

DROP Table Files; 

 

Data:

LOAD '' as Test AutoGenerate 0; 

 

LET NumRows = NoOfRows('Latest'); 

 

FOR i=1 to $(NumRows) 

LET vFileName =FieldValue('FileName',$(i)); 

Concatenate (Data)

LOAD * 

From $(vFileName) (ooxml, embedded labels, table is Sheet1); 

NEXT 

DROP Field Test;

DROP Table Latest;

qlikview979
Specialist
Specialist
Author

Hi Tamil Nagraj,

Thanks for your Support. I got the output

THanks,

Mahesh

tamilarasu
Champion
Champion

Great. Please mark the correct and helpful answers and close this thread. So that it may helps others in future.

Not applicable

Hi Mahesh,

Iam able to get the execute the recent 3 files. Can you please look into it and let me know if you have any issue.

You just need to change the path of the files in the Call function.

Thanks,

Sreeman

qlikview979
Specialist
Specialist
Author

Hi  Experts,

Thanks  for your Support.

tamilarasu
Champion
Champion

Hello Mahesh, 

    Could you please the mark the post that solves your problem by clicking on "Correct Answer" icon. So that the thread considered as closed.