Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
WalidChoukri
Contributor III
Contributor III

Loading two tables only if they share the same date

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 

1 Solution

Accepted Solutions
marcus_sommer

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 

View solution in original post

2 Replies
marcus_sommer

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 

WalidChoukri
Contributor III
Contributor III
Author

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.