Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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();
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;
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.
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
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?
ok so idea is to copy files if all QVDs is refresh with today's date?
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.
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();
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!
Glad that it worked