Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello every one ,
I'm facing this problem, i have two folders each contains different excels workbooks like :
Folder1 :
C:\Users\acer\Desktop\Folder1\ProductionPlan02022021.xls
C:\Users\acer\Desktop\Folder1\ProductionPlan03022021.xls
C:\Users\acer\Desktop\Folder1\ProductionPlan04022021.xls
Folder2:
C:\Users\acer\Desktop\Folder2\Demand01022021.xls
C:\Users\acer\Desktop\Folder2\Demand02022021.xls
I'm trying to find a way to load the latest files of each folder sharing the same date in this case it's 02/02/2021.
I used FileTime function and i can easily get the latest file of each folder however i couldn't find a way to search in both paths any ideas ?
Thank you so much
I'm not sure that there is really an easy way to detect the latest common file respectively date from all folders (it's a comparing of two arrays). Here a method which could work in Qlik (could means it's just written down without any testing and you may need some adjustments here and there):
for each folder in '1', '2'
for each file in filelist('C:\...\folder$(folder)\*.xls')
let vDateOrigin = keepchar(subfield('$(file)', '\', -1), '0123456789'));
let vDateAdjusted = date(date#(keepchar(subfield('$(file)', '\', -1), '0123456789')), 'DDMMYYYY'));
t1: load $(vDateOrigin) as F1, '$(vDateAdjusted)' as F2, $(folder) as F3 autogenerate 1;
next
next
t2: load F1, F2 resident t1 where F3 = 1; inner join(t2) load F1, F2 resident t1 where F3 = 2;
t3: load date(max(F2)) as F2max resident t2;
let vFinalFile = fieldvalue('F1', fieldindex('F2', peek('F2max', 0', 't3'));
drop tables t1, t2, t3; let vDateOrigin = null(); let vDateAdjusted = null();
t1: load * from [C:\...\folder1\*$(vFinalFile).xls] (biff, …);
t2: load * from [C:\...\folder2\*$(vFinalFile).xls] (biff, …);
- Marcus
I'm not sure that there is really an easy way to detect the latest common file respectively date from all folders (it's a comparing of two arrays). Here a method which could work in Qlik (could means it's just written down without any testing and you may need some adjustments here and there):
for each folder in '1', '2'
for each file in filelist('C:\...\folder$(folder)\*.xls')
let vDateOrigin = keepchar(subfield('$(file)', '\', -1), '0123456789'));
let vDateAdjusted = date(date#(keepchar(subfield('$(file)', '\', -1), '0123456789')), 'DDMMYYYY'));
t1: load $(vDateOrigin) as F1, '$(vDateAdjusted)' as F2, $(folder) as F3 autogenerate 1;
next
next
t2: load F1, F2 resident t1 where F3 = 1; inner join(t2) load F1, F2 resident t1 where F3 = 2;
t3: load date(max(F2)) as F2max resident t2;
let vFinalFile = fieldvalue('F1', fieldindex('F2', peek('F2max', 0', 't3'));
drop tables t1, t2, t3; let vDateOrigin = null(); let vDateAdjusted = null();
t1: load * from [C:\...\folder1\*$(vFinalFile).xls] (biff, …);
t2: load * from [C:\...\folder2\*$(vFinalFile).xls] (biff, …);
- Marcus
Thank you so much for your reply, I did something similar and it works mainly storing the common dates using subfield(to extract the date from the filename) and lookup(to get the common dates) in a table then order by to get the first sorted value with the most recent date.
Again Thank you Marcus.