topic Re: Loading two tables only if they share the same date in QlikView

Loading two tables only if they share the same date

WalidChoukri — Thu, 04 Feb 2021 15:17:54 GMT

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

Re: Loading two tables only if they share the same date

marcus_sommer — Fri, 05 Feb 2021 11:07:47 GMT

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

Re: Loading two tables only if they share the same date

WalidChoukri — Mon, 08 Feb 2021 09:05:10 GMT

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.