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: 
abubakarsiddiq7
Contributor III
Contributor III

how to load latest csv file from shared folder in qlik Sense

hi ,

i have a  n no.of csv files in a folder i have to load only latest(Monthly Refresh) csv file from it .

format of CSV file is :      Daily_Sales_Tracker_QV_MCT_20190510

                                               Daily_Sales_Tracker_QV_MCT_20190409

                                                Daily_Sales_Tracker_QV_MCT_20190307

1 Solution

Accepted Solutions
kaanerisen
Creator III
Creator III

Hi,

How about this;

set vFolderPath='lib://testFolder';

FOR Each vFile in FileList('$(vFolderPath)/Daily_Sales_Tracker_QV_MCT_*.csv')
  FileList:
  LOAD 
  '$(vFile)' as FilePathName,
  subfield(SubField('$(vFile)','_',-1),'.',1) AS Date
  AUTOGENERATE 1;   
NEXT vFile;

NoConcatenate

OrderedFileList:
load  FilePathName as File,Date as FileDate Resident FileList order by Date desc;
drop table FileList;

let vDataFile = Peek('File',0,'OrderedFileList');

trace '$(vDataFile)';

drop table OrderedFileList;

DATA:
LOAD
 *
FROM [$(vDataFile)]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Untitled.pngUntitled_1.png

View solution in original post

6 Replies
JordyWegman
Partner - Master
Partner - Master

Hi Abubakar,

Try this:

for each Ext in 'txt' 	
	for each File in filelist (Root & '*.' & Ext) 
			let vFileName = subfield('$(File)','\',-1);
			let vLoadDate = left(SubField('$(vFileName)','_',6),8);
			concatenate (DatesToProcess)
			load
				'$(vLoadDate)'		as LoadDate,				
				'$(vFileName)'		as QvdName,		
			autogenerate 1; 
	next File 
next Ext

You have to create an inline table (DatesToProcess) for this.

Now when you have all the dates, you can get the max value from the dates and get another load statement for loading the max file.

Jordy

Climber 

Work smarter, not harder
abubakarsiddiq7
Contributor III
Contributor III
Author

i don't see any data after loading it  also no  error ran successfully  

JordyWegman
Partner - Master
Partner - Master

What do you see when you use the debugger? Do you see that it retrieves something?

Jordy

Climber

Work smarter, not harder
kaanerisen
Creator III
Creator III

Hi,

How about this;

set vFolderPath='lib://testFolder';

FOR Each vFile in FileList('$(vFolderPath)/Daily_Sales_Tracker_QV_MCT_*.csv')
  FileList:
  LOAD 
  '$(vFile)' as FilePathName,
  subfield(SubField('$(vFile)','_',-1),'.',1) AS Date
  AUTOGENERATE 1;   
NEXT vFile;

NoConcatenate

OrderedFileList:
load  FilePathName as File,Date as FileDate Resident FileList order by Date desc;
drop table FileList;

let vDataFile = Peek('File',0,'OrderedFileList');

trace '$(vDataFile)';

drop table OrderedFileList;

DATA:
LOAD
 *
FROM [$(vDataFile)]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Untitled.pngUntitled_1.png

abubakarsiddiq7
Contributor III
Contributor III
Author

it is working fine .

 

Thanks 

RockyRocker9
Contributor
Contributor

Hi All,

How to calculate the "Previous Sales" from previous file data by selecting the current data FileDate OR Sales from the filter.

Means, we have "Sales_Data_25032019.xlxs" and "Sales_Data_15072019.xlxs" files currently. We will add more files in the future.

I have loaded the files and pulled the FileName, FileDate and "Sales" fields using Subfield function.

Data files having the fields like SaleID, Amount, Customer, Product, DateOfPurchase ...etc . Both the excel files having the same structure.

Requirement:  By selecting the any sales from Sales filter

** 1. We need to calculate the Previous  ---> SUM({<Sales ={'Mar-19'}>} Amount) , compare with the 

                                                              Current  --- >  SUM({<Sales ={'Jul-19'}>} Amount) and calculate the Difference.

Explanation: By selecting the "Jul-2019" from the Sales filter, need to get the "Mar-2019" SUM(Amount) as Previous and end result should be like -- >  SUM({<Sales ={'Jul-19'}>} Amount) - SUM({<Sales ={'Mar-19'}>} Amount)  as Difference

** 2. We need to calculate the No. of users who purchased the same product in both the sales(Current&Previous Sales) to know the product wise repeater customers.

Issue: How to get the Previous records from previous data file dynamically by selecting the current values.

Thanks,

Rocky