Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

abubakarsiddiq7
New Contributor

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

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

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

6 Replies
Partner
Partner

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

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
New Contributor

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

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

Partner
Partner

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

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

Jordy

Climber

Work smarter, not harder
kaanerisen
Contributor III

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

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

Highlighted
abubakarsiddiq7
New Contributor

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

it is working fine .

 

Thanks 

RockyRocker9
New Contributor

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

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