Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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
i don't see any data after loading it also no error ran successfully
What do you see when you use the debugger? Do you see that it retrieves something?
Jordy
Climber
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);
it is working fine .
Thanks
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