topic Re: Loop in script to confirm QVD has been updated in QlikView

Loop in script to confirm QVD has been updated

CNH_1978 — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Loop in script to confirm QVD has been updated

Kushal_Chawda — Mon, 10 Aug 2020 10:56:29 GMT

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;

Re: Loop in script to confirm QVD has been updated

CNH_1978 — Mon, 10 Aug 2020 11:23:30 GMT

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.

Re: Loop in script to confirm QVD has been updated

Kushal_Chawda — Mon, 10 Aug 2020 11:47:27 GMT

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

Re: Loop in script to confirm QVD has been updated

CNH_1978 — Mon, 10 Aug 2020 15:15:24 GMT

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?

Re: Loop in script to confirm QVD has been updated

Kushal_Chawda — Mon, 10 Aug 2020 15:20:14 GMT

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

Re: Loop in script to confirm QVD has been updated

CNH_1978 — Mon, 10 Aug 2020 15:25:05 GMT

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.

Re: Loop in script to confirm QVD has been updated

Kushal_Chawda — Mon, 10 Aug 2020 20:37:57 GMT

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();

Re: Loop in script to confirm QVD has been updated

CNH_1978 — Tue, 11 Aug 2020 08:26:45 GMT

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!

Re: Loop in script to confirm QVD has been updated

Kushal_Chawda — Tue, 11 Aug 2020 08:52:10 GMT

Glad that it worked

Re: Loop in script to confirm QVD has been updated

CNH_1978 — Mon, 17 Aug 2020 08:54:26 GMT

Hi Kush,

Is there an easy way to add another condition into this script. What i am thinking is, if the reload takes more than 1 hour then exit script. I am concerned that if something happens and the file copy hangs then the script will carry on looping until someone stops it. But we will not know straight away if that happens as the task will still be running.

Re: Loop in script to confirm QVD has been updated

Kushal_Chawda — Mon, 17 Aug 2020 12:00:29 GMT

Yes. You can put condition to limit it to loop for one hour from when it got started. But if still condition is not met then do you need to fail the app? Because if you just do exit script it will show successful in task.