Help! concatenation of qvds by day into one

[ioannagr]

Hello guys!

I have made qvds by day like  qvd_01.01.2000 to qvd_24.05.2021 so as to handle a big table.

Now I want to concatenate them into one.

I tried 

LOAD * 

 FROM [$(vPathQVD)qvd_*.qvd]
(qvd);

Yet it returns 0 rows.

(Qlik stored them in folders in a very weird way in the path, like first it did the days then the months, then the year

for example, qvd_01 -> qvd_01.01, qvd_01.02, etc (first day of all months ) -> qvd_01.01.2000, qvd_01.01.2001, qvd_01.01.2002 etc , 

qvd_02 -> qvd_02.01, qvd_02.02, etc (second day of all months ) -> qvd_01.01.2000, qvd_01.01.2001, qvd_01.01.2002 etc)

Help please?

[eddie_wagt]

Hello @ioannagr ,

The syntax should be correct. I tested your code with pseudo qvd's. Did you verify if the QVD's have data and did you check the path in the variable vPathQVD ? 

Regards Eddie

[ioannagr]

Hi @eddie_wagt  yes I have! 

LET a=Date#('01/01/2000', 'DD/MM/YYYY'); 
Let b=Date#(today()-1, 'DD/MM/YYYY');

Trace $(a);
do while a < b
a1 = date(a, 'DD/MM/YYYY');

trace $(a1);

LOAD
*
FROM [l$(vPathQVD)/qvd_$(a1).qvd]
(qvd);

LET a=a+1;

loop

With the above script it goes from my starting date to yesterday but not all dates have qvds because nothing happened on this date, how do i not take these days into account so that the script doesn't fail?

[eddie_wagt]

Hello @ioannagr ,

Hey this is different code? It looks something right, but variable a1 does not meet the fileformat?

these are your files: qvd_01.01.2001 and a1 has output qvd_01/01/2001

If  your are not using LOAD * from qvd_*.qvd (qvd) and if your want to use the loop then I suggest to check variable a1 and you could use the function QvdCreateTime function. If file don't exists, returns NULL.

So something like this

LET a=Date#('01/01/2000', 'DD/MM/YYYY'); 
Let b=Date#(today()-1, 'DD/MM/YYYY');

Trace $(a);
do while a < b
a1 = date(a, 'DD/MM/YYYY');



if not isnull(QVDCreateTime('$(vPathQVD)$(a1)')) then

trace $(a1) is not empty and is being loaded;

LOAD
*
FROM [l$(vPathQVD)/qvd_$(a1).qvd]
(qvd);

ENDIF


LET a=a+1;

[ioannagr]

Hi @eddie_wagt  so that means that if i have data for 01/01/2000 but next i have on 28/01/2000 it will not fail but still show it with trace as :

01/01/2000 lines fetched: 200

02/01/2000

03/01/2000

04/01/2000

...

28/01/2000  lines fetched: 10?

Is there a way to not take them into account at all?

Many thanks 😁

[ioannagr]

Hi @eddie_wagt  tried your way, this doesn't work for me, it just goes in a loop of dates without loading data. Can you point me to a way of concatenating them? Like you saw on my first post, Qlik stored data in a very weird way so I guess that's why 

LOAD * 

 FROM [$(vPathQVD)qvd_*.qvd]
(qvd); 

doesn't work either.

[ioannagr]

@Taoufiq_Zarra please kindly look at this, I think i could use your help! The way Qlik stored the daily data into subfolders by DD, then MM, then YYYY has me really confused. 😞

[ioannagr]

Maybe also gentlemen @Vegar , @hic  could help me on this 🙂 Thank you in advance.

[marcus_sommer]

Just to consider the existing files within folders you could use dirlist() and filelist(). Take a look on the help by "for each" because there is a good example how to loop over multiple folders and fetch just those files with a certain name-pattern and/or extension and additionally you may apply within the loop before starting the load multiple further checks on the filename or using functions like filesize() or similar measures.

Beside this it might be useful to re-create all the daily qvd's with a proper filename / foldernames by reversing the date-logic in the names from DD.MM.YYYY to YYYYMMDD.

- Marcus

[ioannagr]

@marcus_sommer  thanks for the prompt response, I have 2 issues/ questions.

1)So, if i store them like YYYYMMDD, Qlik won't store them in the way they are now, by DD, by MM, by YYYY?

2) Looked through the help already and tried something like

(In 'General' there are all qvd_01 to qvd_31 and in them all the subfolders)

Let Root='D:\QVDFiles\QVDs\General\';     //DOES THIS  WORK WITH LIB CONNECTION as well?
Sub ScanFolder(Root)
For Each FoundFile in Filelist( Root & '\*' & '.qvd')
If '$(FoundFile)' Like '*qvd_*' Then
// Data:
Load *
FROM [$(FoundFile)] (qvd);
End If

Next FoundFile

// Sub folder
For Each SubDirectory in Dirlist( Root & '\*' )
Call ScanFolder(SubDirectory)
Next SubDirectory

End Sub

Call ScanFolder('D:') ;    // NOT SURE about what to put here?

So far, nothing happens. Please advise me 😕