Loop in script to confirm QVD has been updated

[CNH_1978]

Hi everyone. 

I am looking for some help to write a simple loop in my script which confirms a QVD has the latest date stamp (QVDCreateTime) before the script exits. 

I have the following which checks the date stamp 

let vQVDPath = 'C:\Users\USER1\Desktop\';
let vToday = date(today(),'DD/MM/YYYY');

if left(date(QvdCreateTime('$(vQVDPath)TestQVD.QVD'),'DD/MM/YYYY'),10) = '$(vToday)' then

SET vResult = 'Yes';

ELSE

SET vResult = 'No';

ENDIF;

What i want to do now is wrap a loop around it so it keeps looping until the condition is met. 

Can anyone help please?

[Kushal_Chawda]

try below

let vQVDCreateTime = floor(QvdCreateTime('$(vQVDPath)TestQVD.QVD'));

let vToday = floor(today());

do UNTIL vQVDCreateTime = vToday

if vQVDCreateTime = vToday then

SET vResult = 'Yes';

TRACE $(vResult);

EXIT do;

ELSE

SET vResult = 'No';

TRACE $(vResult);

ENDIF;

[CNH_1978]

Hi Kush,

Thanks for your reply. I have tried your suggestion but i can't get it to work. 

I have attached the QVW, are you able to see what i have done wrong please?

Also, how does the script know to go back to the start of the loop if the QVD date does not = the todays date?

Currently the result is stating 'No' but the script ends instead of looping through again. 

[Kushal_Chawda]

If QVD crete time equal to today then do you want to perform something? or do you want to perform when it is not equal

[CNH_1978]

Hi Kush,

So the reason for this loop is as follows. 

We have QlikView server (not publisher), we have a QVW that executes a batch file that copies 20+ QVDs from a remote server to the local server. 

The QVW in question runs from the QMC with a chain of tasks underneath. 

When the QVW is running and the batch file is executed, the task finishes and the next task in the list starts. The problem is that even though the task finishes (according to the QMC), it hasn't actually finished. The QMC does not know when the batch file commands are complete, so the tasks that run after it have loaded in old data because the new QVDs are still in the process of copying across. 

So i thought i could put a check in the script of the QVW to check that the QVDs in the local folder have been updated with today's QVDs. 

The idea was to execute the batch file, then start a loop where it keeps checking each QVD in the local folder and does not exit until all QVDs are today's date. 

Currently i am trying to get it working by checking a single QVD in the local folder, but the goal is to check all QVDs in the local folder. 

Does that make sense?

[Kushal_Chawda]

ok so idea is to copy files if all QVDs is refresh with today's date?

[CNH_1978]

Yes that's correct. 

Execute a batch file (which i have done)

then to check that all QVDs in the local directory have today's timestamp. I do not want the reload to complete until all QVDs are updated. 

That will then stop the tasks from starting in the QMC until all of the QVDs have been copied.

[Kushal_Chawda · Accepted solution]

This loop will run until all files are not updated meaning that it will check this forever until condition met.  You cam limit the loop to run for specific hours as enhancement.

let vUpdateQVDFlag= Null();

Do UNTIL vUpdateQVDFlag=1

Sub DoDir (Root)
     For Each Ext In 'qvd' // filetype to search for in current directory
          For Each File In FileList (Root & '\*.' & Ext)
                    Files:
                    Load '$(File)' as FileName,
                        RowNo() as Row,
                     floor(FileTime('$(File)')) as FileTimeNum,
                     FileTime('$(File)') as FileTime
                    Autogenerate 1;
          Next File
     Next Ext
     For Each Dir In DirList (Root & '\*') // searching in subdirs
          Call DoDir (Dir)
     Next Dir
End Sub
Call DoDir ('C:\Data'); // Change the actual path of your QVD folder

FilesCheck:
LOAD if(QVDCount=UpdateQVDCount,1,0) as UpdateQVDFlag;
LOAD max(Row) as QVDCount,
     sum(if(FileTimeNum=floor(Today()),1,0)) as UpdateQVDCount
Resident Files;

let vUpdateQVDFlag = Peek('UpdateQVDFlag',0,'FilesCheck');

if vUpdateQVDFlag=1 THEN

TRACE "All Files are upto date";

exit do

ELSE

TRACE "All Files are not updated yet";

DROP Tables FilesCheck,Files;

ENDIF

LOOP

if vUpdateQVDFlag=1 THEN

// Here you can do what you want to perform when all files are updated

EXECUTE;  // you can call your execute command to run batch file.

ENDIF

let vUpdateQVDFlag= Null();

[CNH_1978]

Hi Kush,

Fantastic, I removed the part of code used to search in sub folders (as that is not needed) and tested and it works perfectly. 

Thanks so much for your help! 

[Kushal_Chawda]

Glad that it worked