topic Re: First 3 Sheets Data in QlikView

First 3 Sheets Data

qlikview979 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: First 3 Sheets Data

jonathandienst — Fri, 29 Apr 2016 05:05:16 GMT

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.

Re: First 3 Sheets Data

tamilarasu — Fri, 29 Apr 2016 06:29:27 GMT

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.

Re: First 3 Sheets Data

Kushal_Chawda — Fri, 29 Apr 2016 06:41:41 GMT

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.

Re: First 3 Sheets Data

tamilarasu — Fri, 29 Apr 2016 06:53:04 GMT

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;

Re: First 3 Sheets Data

qlikview979 — Sat, 30 Apr 2016 08:13:21 GMT

Hi Tamil Nagraj,

Thanks for your Support. I got the output

THanks,

Mahesh

Re: First 3 Sheets Data

tamilarasu — Sat, 30 Apr 2016 08:25:40 GMT

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

Re: First 3 Sheets Data

Sat, 30 Apr 2016 09:30:12 GMT

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

Re: First 3 Sheets Data

qlikview979 — Sat, 30 Apr 2016 09:38:31 GMT

Hi Experts,

Thanks for your Support.

Re: First 3 Sheets Data

tamilarasu — Sat, 30 Apr 2016 12:08:35 GMT

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.