
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to ignore load of QVD with 0 records
Hi All,
I'm stuck with a problem while loading qvd's in my app.
I've split a table QVD in more QVD's, divided by TableName_YYYYMM.qvd partition.
These files start from 202002, anyway i've set a condition in my app to take into consideration just TableName_*.qvd greater or equal than Month(Today) -36.
During these days, i've tried to reload my app and I saw that qlik script, obviously, still keep uploading every qvd part, even though, for example,
202002 part is being loaded with 0 records in it, since my where condition in the table script let me start from 202003.
Is there any method to save time ignoring the load of qvd's with 0 records?
The client asked this for an ODAG Implementation, since a filter of the ODAG is the exact date, so there will be a lot of qvd's part producing 0 records while loading them into the Template App.
Thanks,
Damiano
- Subscribe by Topic:
-
Data Load Editor
-
Developers
-
expression
-
General Question
-
Script
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
vMaxDate isn't a period-information like YYYYMM else it's a date with the formatting of YYYYMM. You may consider to wrap your formatting with text() to keep the string-part and removing the underlying numerical value - but depending on the place and type of call you may there comparing a string against a number. Personally I avoid this potential trouble by creating the period with the following logic:
year(date) * 100 + month(date)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, first you will need to make list of qvd with no. of records and keep only those who have records.
QvdNoOfRecords(filename)
Script:
LET vFile='[TableName_*.qvd]';
FOR EACH vFile in FileList('TableName_*.qvd')
Load * FROM $(vFile)(qvd);
FileList:
Load
'$(vFile)' as FileName,
QvdNoOfRecords('$(vFile)') as NoOfRecords
Autogenerate 1;
Next vFile;
for i=0 to NoOfRows ('FileList')-1
let vTableName=Peek('tablename',$(i),'FileList');
$(vTableName):
Load * from
$(vTableName) where NoOfRecords>0;
Store $(vTableName) Into _____;
Drop Table $(vTableName);
next
You can store it if you want or just remove remove the store statement.
Please Accepts as Solution if it solves your query.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
By just a few 0 record-qvd's the load-time impact won't be significantly. You may rather considering not to create empty qvd's - could be queried before the store - or not to split the data on a daily level else a monthly level. Even more important would be to ensure that the qvd-loads are optimized.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes, there is a way to save time by ignoring the loading of QVDs with 0 records. You can use the EXISTS() function to check if the QVD contains any records before loading it.
Here's an example of how to modify your script to check if the QVD contains any records:
LET vMaxDate = Num(MonthStart(Today()) - 36, 'YYYYMM');
FOR each vQVD in FileList('lib://QVDs/TableName_*.qvd')
LET vQVDDate = Mid('$(vQVD)', Len('$(vQVD)') - 11, 6);
IF vQVDDate >= vMaxDate AND EXISTS('$(vQVD)') THEN
TableName:
LOAD * FROM [$(vQVD)] (qvd);
ENDIF
NEXT
In this example, the EXISTS() function is used to check if the QVD contains any records before loading it. If the QVD contains 0 records, it will not be loaded into the app, saving time and resources.
Note that the FileList() function is used to dynamically load all QVDs that match the pattern "TableName_*.qvd". The LET statement is used to extract the date portion of the QVD filename and compare it to the maximum date allowed based on your condition.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
thank for your reply, but there's something that maybe I'm missing in the script you posted.
I see that you call in it a variable vQVD even though I have no evidence where is it declared; do I have to integrate your script with something else?
My Qvd's are named as following: Tablename_YYYYMM.qvd, does it work with your vMaxDate variable?
Thank you,
Damiano


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The variable vQVD is created/declared here:
FOR each vQVD in FileList()
Instead of the shown exists() check you may just use qvdnoofrecords() within an if-loop to skip empty files.
Like above mentioned there might be other measures with a bigger impact on the run-time as skipping empty files.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @marcus_sommer ,
this is my script now:
LET vMaxDate = date(MonthStart(date(Today(),'YYYYMM'),-36),'YYYYMM');
Trace vMaxDate =$(vMaxDate);
FOR each vQVD in FileList('$(vs_QVD_Store_Path)Gross Req v Acq Rm_*.qvd')
LET vQVDDate = date(date#(Mid('$(vQVD)', Len('$(vQVD)') - 10,4) & Mid('$(vQVD)', Len('$(vQVD)') -5,2),'YYYYMM'),'YYYYMM');
Trace vQVDDate =$(vQVDDate);
IF vQVDDate >= vMaxDate AND EXISTS('$(vQVD)') THEN
TableName:
LOAD * FROM [$(vQVD)] (qvd);
ENDIF
NEXT
This is the result of Trace vMaxDate =$(vMaxDate):
202003
These are the results of Trace vQVDDate =$(vQVDDate):
202003
202004
202005
.....
202303
By the way it seems that this condition
IF vQVDDate >= vMaxDate AND EXISTS('$(vQVD)') THEN
TableName:
LOAD * FROM [$(vQVD)] (qvd);
is never equal to TRUE.
Did i make any mistake?
Damiano


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
vMaxDate isn't a period-information like YYYYMM else it's a date with the formatting of YYYYMM. You may consider to wrap your formatting with text() to keep the string-part and removing the underlying numerical value - but depending on the place and type of call you may there comparing a string against a number. Personally I avoid this potential trouble by creating the period with the following logic:
year(date) * 100 + month(date)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @marcus_sommer ,
that's my script reviewed transforming both variable in numerical
LET vMaxDate = NUM(YEAR(MonthStart(today(),-12))*100+MONTH(MonthStart(today(),-12)));
Trace vMaxDate =$(vMaxDate);
FOR each vQVD in FileList('$(vs_QVD_Store_Path)Gross Req v Acq Rm_*.qvd')
LET vQVDDate = NUM(YEAR(
date(date#(Mid('$(vQVD)', Len('$(vQVD)') - 10,4) & Mid('$(vQVD)', Len('$(vQVD)') -5,2),'YYYYMM'),'YYYYMM'))*100
+
MONTH(
date(date#(Mid('$(vQVD)', Len('$(vQVD)') - 10,4) & Mid('$(vQVD)', Len('$(vQVD)') -5,2),'YYYYMM'),'YYYYMM')));
Trace vQVDDate =$(vQVDDate);
IF vQVDDate >= vMaxDate AND EXISTS('$(vQVD)') THEN
TableName:
LOAD * FROM [$(vQVD)] (qvd);
ENDIF
NEXT
this is the log:
Again, it seems that the only thing nt working is the >= check between variables.
D


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I suggest to replace the exists() with qvdnoofrecords().
Further the extract of the period-information from the file-name might be simplified by subfield(subfield('$(vQVD)', '_', -1), '.', 1) or if the folder/filenames didn't contain any numbers even more with keepchar('$(vQVD)', '0123456789').

- « Previous Replies
-
- 1
- 2
- Next Replies »