Load a sequence of qvd files

[Olya]

Hi all,

I have a folder with daily snapshots qvd files whose have names like "20190628.qvd" or "20190513.qvd", where the file's name means the actual date. 

Now my code looks like this: 

Log:

LOAD *

From [lib://.../20190630.qvd]
(qvd);

Concatenate (Log)

LOAD 

....

From [lib://.../201907*.qvd]
(qvd);

So I could upload the last file from June and all July's files.

My question is what is a way to modify the code in order, for example,  to upload the data from May 01, 2019 and all files whose are older?

Thank you in advance

[marcus_sommer · Accepted solution]

Instead of using wildcards within the filename you could also use a filelist to loop through all available files in your folder. In a second step you could extract the date from the filename and apply your wanted conditions, for example:

for each file in filelist('lib://.../*.qvd')
   if keepchar(subfield('$(file)', '\', -1), '0123456789') >= 20190501 then
      load ...
   end if
next

Qlik-Sense-Load-all-files-from-a-folder

- Marcus

[Olya]

Danke, it works! Just one question, probably the condition should be not ">= 20190531" but just ">", because I have seen that during the last step the Qlik Sense uploaded again the file with the name "20190531 .qvd"?

[marcus_sommer · Accepted solution]

You could either apply multiple conditions as well as various operators for the comparisons. If there are multiple conditions the use of a variable and a switch to a match-function would be useful, like:

let v = keepchar(subfield('$(file)', '\', -1), '0123456789');
if match(-1, $(v) >= 20190501, $(v) < 20190601) then

- Marcus

[Olya]

Thank you, this flexibility is great!

I am also correct that I can't use the variable (it should be integer) for the optional third argument in SubField function?

For example, I would like to make a loop like this:

// Create EdgeID column
EdgeID:
Set Counter = 1;
If $(Counter) < Len(ProcessPath)then
LOAD
CaseID,
subfield(ProcessPath, '-', $(Counter)) & '-' & subfield(ProcessPath, '-',$(Counter)+1) as EdgeID
RESIDENT ProcessPath;
Let Counter = $(Counter) +1;
End If

[marcus_sommer]

There is no problem to use a variable as the third parameter of subfield() and yes it must be an integer.

I must admit that I don't understand your example ... but I assume that there are other and probably more suitable approaches to get the information you want. For example if you want to include path/filename-parts within the load you could use various file-functions like filename(), filebasename(), filepath() ... and on this various string-functions like subfield() and/or substringcount() and/or left/mid/right and so on.

Further if you want to split a string into several rows you could subfield without the third parameter because it creates automatically a loop.

- Marcus

[Olya]

Thank you, I got you 🙂

[Olya]

Hello, I have one more question.

How could I choose only two or four files actual for every month? For example, I would like to download the data actual for every Monday of every month. Thanks!

[marcus_sommer]

You could pick these information also from your filenames, for example with something like this:

let v = keepchar(subfield('$(file)', '\', -1), '0123456789');
   if match(-1, $(v) >= 20190501, $(v) < 20190601) then
      if(weekday(date#($(v), 'YYYYMMDD')) = 1 then

- Marcus