How to ignore load of QVD with 0 records

[DamianoD]

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

[neerajthakur]

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.

[marcus_sommer]

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.

[alexanderbrown254b]

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.

Milestoneapply

[DamianoD]

Hi @alexanderbrown254b,

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

[marcus_sommer]

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.

[DamianoD]

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

[marcus_sommer · Accepted solution]

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)

[DamianoD]

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

[marcus_sommer]

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').