Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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
Highlighted

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
Highlighted

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.

Highlighted
MVP
MVP

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.

Highlighted

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

Highlighted
Specialist
Specialist

Hi Tamil Nagraj,

Thanks for your Support. I got the output

THanks,

Mahesh

Highlighted

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

Highlighted
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

Highlighted
Specialist
Specialist

Hi  Experts,

Thanks  for your Support.

Highlighted

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.