Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
pdasmajumdar
Contributor II
Contributor II

Qlik Sense Load Last 5 years of QVD data from sub folders into a single table

Hello Everyone!

I have 2 sub-folders with QVDs partitioned by Year. Data goes back to 20 years. I need to load the latest 5 years of data from both the sub folders (i.e. 5 years of files from each folder, so total of 10 QVD files) and kind of concatenate them. I am aware that if the field structure is same in all files, it will concatenate directly. But how do it do that for files in 2 different locations and only for latest 5 years of data. I have added a screenshot for the questions. Sub Folder 1 and Sub Folder 2 each have 20 files. And I want to pull in the last 5 years of QVD (2018-2022) from each of these folders and concatenate the data into 1 single table  to use for front end development. 

P.S. The subfolders only include rolling 20 years of data. So I cannot hard code Year values in file list as next year I will need to pull in data from 2019-2023 and then 2020-2024 and so forth.

Labels (1)
3 Replies
pdasmajumdar
Contributor II
Contributor II
Author

Adding screenshots for reference

gp_oconnor
Partner - Contributor III
Partner - Contributor III

Something like this [haven't tested this code, but the structure has been used before]:

let zStartYear=Year(Today()) - 5;

for each zFolderName in 'Sub Folder1', 'Sub Folder2'

  for each zFileName in FileList('$(zConnector)/$(zFolderName)')

    if Right(SubField('$(zFileName)','.',1),4) >= '$(zStartYear)' then

      'LoadData':

      load *

      from [$(zFileName)] (qvd)

      ;

     end if;

  next zFileName;

next zFolderName;

pdasmajumdar
Contributor II
Contributor II
Author

This worked great! I updated it according to how I wanted my field list and variables and it worked out as expected. Thank you!