topic Re: Looping in a field in Script in QlikView

Looping in a field in Script

azmeerrehan — Mon, 02 Jul 2018 21:35:04 GMT

I have created a QVW which checks for today's date in an Audit table from Database. If Today's Date exits in the table then it suppose to exit which will trigger the next load to start, but if the date is not today it loops every 5 secs to check for today's date. I would like to add the below 2 things:

1) If the date is not today then check every 5 secs do it 5 times and then exit with a load failure.

LET vDWDate = null();

LET vToday = num(today());

// Start loop script that will continue until condition is met.

Do until vDWDate=vToday

Trigger:

Select DISTINCT DATE_TIME as DWDate

FROM AUDIT_DATA

where DATE_TIME = (Select max(DATE_TIME) FROMFROM AUDIT_DATA) ;

LET vDWDate = peek('DWDate');

// where condition is met, exit and complete.

if vDWDate=vToday then

Exit Do

// where condition is not met, drop that table ready to restart the load process.

else

DROP TABLE Trigger;

// The sleep means that we can control the frequency of calls to the source database (or file).

Sleep 5000; // 10000 milliseconds = 10 seconds.

end if

// where condition is not met then restart loop.

loop

exit script;

Re: Looping in a field in Script

tamilarasu — Tue, 03 Jul 2018 02:44:29 GMT

HI Rehan,

LET vDWDate = null();
LET vToday = num(today());
Set vCounter = 1;

// Start loop script that will continue until condition is met.
Do until vDWDate=vToday

Trigger:
Select DISTINCT DATE_TIME as DWDate
FROM AUDIT_DATA
where DATE_TIME = (Select max(DATE_TIME) FROMFROM AUDIT_DATA);

LET vDWDate = peek('DWDate');

// where condition is met, exit and complete.
if vDWDate=vToday then

Exit Do

// where condition is not met, drop that table ready to restart the load process.

elseif vCounter =5 then

    TRACE 'Date does not exist!!';   //Manual error message that will be captured in log
    SET TriggerError; //This line of code triggers an error
    Exit Do //or exit script;

Else

    DROP TABLE Trigger;
   //The sleep means that we can control the frequency of calls to the source database (or file).
    Sleep 5000; // 10000 milliseconds = 10 seconds.
    Let vCounter = vCounter +1;   //Increasing counter on each load

ENDIF

// where condition is not met then restart loop.
loop

exit script;