Reload only when new data is available

[gvlvijay]

Hi experts,

Hope you are doing good.

I have a requirement to execute the Qliksense script only when the data is available in the update table. Below is what i have done so far.

Table 1: Direct load from DB and pulls the maximum date

Table 2: Load from QVD and pulls the maximum data

LET vcurrentdate = peek('vcurrentdate',0,'vcurrentdate');

LET vpreviousdate = peek('vpreviousdate',0,'vpreviousdate');

After this i am assigning a new variable value to check if the current date is greater than the previous date i.e., 

If $(vcurrentdate) > $(vpreviousdate) then 

LET vupdate = 'True'

else 

Let vupdate = 'False'

END If;

// Starting to check the db update

Do while vupdate = 'False'

If vupdate = 'True' Then

Exit Script

Else

Sleep (10000)

Let vupdate = '';

// Again loading the date from Table 1 & 2 here

Table1:

Load Max(date) as currentdate

From sql.updatetable;

Table 2:

Load Max(date) as previousdate

From Date.qvd;

// Again checking and assigning the dates

LET vcurrentdate = peek('vcurrentdate',0,'vcurrentdate');

LET vpreviousdate = peek('vpreviousdate',0,'vpreviousdate');

If $(vcurrentdate) > $(vpreviousdate) then 

LET vupdate = 'True'

else 

Let vupdate = 'False'

END If;

END IF;

Exit Do when vupdate = 'True'

Loop;

// 

Problem i am facing now is it is going into infinite loop even when the vcurrent date is updated while the app reload is in process. Once i stop and re-run the application then it is working since it is satisfying the condition. I am not sure as what is the problem here.

Any help on the same is highly appreciated. Thanks in advance.

*PS - I am not in a position to post / share any data as it is from a highly restricted source. Please excuse and hope you understand.

[Sayed_Mannan]

I refined your code a little bit, try this :

//Initial Load and Variable Assignment:
//Load the maximum dates from the database and QVD.
//Assign these dates to variables.
Table1:
Load Max(date) as currentdate
From sql.updatetable;

Table2:
Load Max(date) as previousdate
From Date.qvd;

LET vcurrentdate = peek('currentdate', 0, 'Table1');
LET vpreviousdate = peek('previousdate', 0, 'Table2');

//Check for Data Update:
//Compare the dates and set the vupdate variable accordingly.
IF $(vcurrentdate) > $(vpreviousdate) THEN
LET vupdate = 'True';
ELSE
LET vupdate = 'False';
END IF;

//Loop to Check for Updates:
//Use a loop to periodically check for updates, but ensure it exits correctly.
DO WHILE $(vupdate) = 'False'
Sleep 10000; // Wait for 10 seconds before checking again

// Reload the dates
Table1:
Load Max(date) as currentdate
From sql.updatetable;

Table2:
Load Max(date) as previousdate
From Date.qvd;

// Reassign the dates
LET vcurrentdate = peek('currentdate', 0, 'Table1');
LET vpreviousdate = peek('previousdate', 0, 'Table2');

// Check for update
IF $(vcurrentdate) > $(vpreviousdate) THEN
LET vupdate = 'True';
ELSE
LET vupdate = 'False';
END IF;

// Exit loop if update is found
IF $(vupdate) = 'True' THEN
EXIT DO;
END IF;
LOOP;

//Exit Script:
//Ensure the script exits correctly once the update is detected.
IF $(vupdate) = 'True' THEN
EXIT SCRIPT;
END IF;

[gvlvijay]

Hi Sayed,

Thank you for taking time in replying.

Unfortunately the script is still looping even if the data is updated.

I tried to set the variables to NULL before starting the DO WHILE in case if that was the problem but still the script is running in infinite loop.

/Check for Data Update:
//Compare the dates and set the vupdate variable accordingly.
IF $(vcurrentdate) > $(vpreviousdate) THEN
LET vupdate = 'True';
ELSE
LET vupdate = 'False';
END IF;

// Updated below code

IF vupdate = 'False' THEN

LET vcurrentdate = NULL();

LET vpreviousdate = NULL();

END IF;

//Loop to Check for Updates:
//Use a loop to periodically check for updates, but ensure it exits correctly.
DO WHILE $(vupdate) = 'False'
Sleep 10000; // Wait for 10 seconds before checking again

[marcus_sommer]

You could avoid it by changing the loop-type, maybe: for i = 1 to n and also using the iterator to adjust the sleep like: sleep 10000 * $(i);

[gvlvijay]

Hi all, thank you for your inputs.

I found the issue and was able to achieve the loop exit once the update is available in DB.

I simply changed the table reference before and after the loop has started.

Table 1: Direct load from DB and pulls the maximum date

Table 2: Load from QVD and pulls the maximum data

LET vcurrentdate = peek('vcurrentdate',0,'vcurrentdate');

LET vpreviousdate = peek('vpreviousdate',0,'vpreviousdate');

After this i am assigning a new variable value to check if the current date is greater than the previous date i.e., 

If $(vcurrentdate) > $(vpreviousdate) then 

LET vupdate = 'True'

else 

Let vupdate = 'False'

END If;

// Starting to check the db update

Do while vupdate = 'False'

If vupdate = 'True' Then

Exit Script

Else

Sleep (10000)

Let vupdate = '';

// Again loading the date from Table 1 & 2 here, changed the table name to 3 & 4

Table3:

Load Max(date) as currentdate

From sql.updatetable;

Table 4:

Load Max(date) as previousdate

From Date.qvd;

// Again checking and assigning the dates

LET vcurrentdate = peek('vcurrentdate',0,'vcurrentdate');

LET vpreviousdate = peek('vpreviousdate',0,'vpreviousdate');

If $(vcurrentdate) > $(vpreviousdate) then 

LET vupdate = 'True'

else 

Let vupdate = 'False'

END If;

END IF;

Exit Do when vupdate = 'True'

Loop;