topic Re: Waiting for data source to update data while refresh is running in QlikView

Waiting for data source to update data while refresh is running

brennanbd — Wed, 05 Jun 2013 18:02:40 GMT

I am in an iSeries data environment where I'm trying to wait for batch jobs to finish on the server before attempting to load the data into Qlikview. I have this solution working fine in a non-Qlikview environment, and I'm attempting to replicate this report in Qlikview. Here is how it's supposed to work:

1. Check the system for the max load date in the file.

2. If the max load date is equal to yesterday, proceed with the load (data is ready).

3. If the max load date is not equal to yesterday, wait 15 minutes and try step #2 again.

4. Repeat step #3 five times, then abort the process if no data is available.

The code below works correctly if I don't try to change the RECORDDT while it's in the do while loop. I've used the Debug process and seen that if I update the RecordDt on the back end system while the do while is running, and the vRecordDt value changes to be the same as the VYesterdayAS400 variable, the loop continues running until it gets to vCounter=5. The whole point of waiting is to see if the data has loaded on the back end system and then proceeding with the load.

LOAD RECORDDT;
SQL 
SELECT MAX(YDAYDT) AS RECORDDT
FROM  Data.File1;
Let vYesterdayAS400 = date(now()-1,'1YYMMDD');
Let vCounter = 1;
Let vRecordDt = peek('RECORDDT');
do while ($(vRecordDt) <> $(vYesterdayAS400)) and (vCounter < 5)
  sleep 2000; // ideally sleep for 15 minutes which is 900000, but in testing sleep for 2 seconds
  drop field RECORDDT;
  LOAD RECORDDT;
  SQL SELECT MAX(YDAYDT) AS RECORDDT FROM Data.File1;
  Let vRecordDt = peek('RECORDDT');
  Let vCounter = vCounter + 1;
loop
...do the rest if the data is there...

Am I missing something obvious? Is there a better way to accomplish my goal?

Thanks in advance,

Billy

Re: Waiting for data source to update data while refresh is running

swuehl — Wed, 05 Jun 2013 20:04:04 GMT

Try

do while (vRecordDt <> vYesterdayAS400) and (vCounter < 5)

and double check the variables and their format while executing the script (e.g. using the debugger and / or TRACE statements).

Hope this helps,

Stefan

Re: Waiting for data source to update data while refresh is running

brennanbd — Wed, 05 Jun 2013 20:17:23 GMT

Stefan,

I tried using a comparison without the $(v...) and it doesn't work. I've used debugging many times trying to troubleshoot this issue, and the variables are coming through with expected values. So I'm still stuck.

Billy

Re: Waiting for data source to update data while refresh is running

swuehl — Wed, 05 Jun 2013 20:39:46 GMT

And your AS400 date is really formated with a leading '1' (like you formated your yesterday's date)?

Could you post your execution log (add TRACE statements if needed to show the variable values)?

I would also try adding table name labels to your table loads and using these table names in your peek() functions as third argument.

Re: Waiting for data source to update data while refresh is running

brennanbd — Wed, 05 Jun 2013 21:37:08 GMT

I have figured out a solution, but I'll first post information related to the original code set because in theory it should work. Stefan, yes the date format is correct - the AS400 we have uses a CYYMMDD value where C is 0 for 19, 1 for 20, 2 for 21, and so on. We're good until the year 3000, but by then I won't care

Here is my original code with trace statements added:

trace Step 1 - Load initial source data;
LOAD RECORDDT;
SQL 
SELECT MAX(YDAYDT) AS RECORDDT
FROM  Database.File1;
Let vCounter = 1;
Let vRecordDt = peek('RECORDDT');
trace Step 2 - vYesterdayAS400 value=$(vYesterdayAS400);
trace Step 3 - vRecordDt value=$(vRecordDt);
trace Step 4 - About to enter do while loop;
do while ($(vRecordDt) <> $(vYesterdayAS400)) and (vCounter < 5)
  trace Step 5 - Inside do while loop;
  sleep 2000; // 900000 sleep for 15 minutes waiting for data to be ready
  drop field RECORDDT;
  trace Step 6 - Reloading data source to see if updated;
  LOAD RECORDDT;
  SQL SELECT MAX(YDAYDT) AS RECORDDT FROM Database.File1;
  Let vRecordDt = peek('RECORDDT');
  Let vCounter = vCounter + 1;
  trace Step 7 - new Recorddt value=$(vRecordDt);
  trace Step 8 - vYesterdayAS400 value=$(vYesterdayAS400);
  trace Step 9 - vCounter=$(vCounter);
loop
trace Step 11 - Outside do while loop;
trace Step 12 - vCounter=$(vCounter);
trace Step 13 - Recorddt value=$(vRecordDt);
trace Step 14 - vYesterdayAS400 value=$(vYesterdayAS400);

Here is the execution log using Debug mode:

Connecting to dataconnection

Connected

Step 1 - Load initial source data

File1 lines fetched

Step 2 - vYesterdayAS400 value=1130604

Step 3 - vRecordDt value=1130603

Step 4 - About to enter do while loop

Step 5 - Inside do while loop

------ I changed the data in the data source so the Recorddt value = vYesterdayAS400 value ------

Step 6 - Reloading data source to see if updated

File1 lines fetched

Step 7 - new Recorddt value=1130604

Step 8 - vYesterdayAS400 value=1130604

Step 9 - vCounter=2

------ Should have exited the do while loop here, but it didn't ---------

Step 5 - Inside do while loop

Step 6 - Reloading data source to see if updated

File1 1 lines fetched

Step 7 - new Recorddt value=1130604

Step 8 - vYesterdayAS400 value=1130604

Step 9 - vCounter=3

Step 5 - Inside do while loop

Step 6 - Reloading data source to see if updated

File1 1 lines fetched

Step 7 - new Recorddt value=1130604

Step 8 - vYesterdayAS400 value=1130604

Step 9 - vCounter=4

Step 5 - Inside do while loop

Step 6 - Reloading data source to see if updated

File1 1 lines fetched

Step 7 - new Recorddt value=1130604

Step 8 - vYesterdayAS400 value=1130604

Step 9 - vCounter=5

Step 11 - Outside do while loop

Step 12 - vCounter=5

Step 13 - Recorddt value=1130604

Step 14 - vYesterdayAS400 value=1130604

...additional data steps

--- Script Finished ---

THIS is the code that actually works:

LOAD RECORDDT;
SQL 
SELECT MAX(YDAYDT) AS RECORDDT
FROM  database.file1;
  
Let vCounter = 1;
Let vRecordDt = peek('RECORDDT');
trace Step 2 - vYesterdayAS400 value=$(vYesterdayAS400);
trace Step 3 - vRecordDt value=$(vRecordDt);
  
If ($(vRecordDt) = $(vYesterdayAS400)) Then
          Let vMatchValues = 'Y';
else
          Let vMatchValues = 'N';
endif
trace show vmatchvalues = $(vMatchValues);
trace Step 4 - About to enter do while loop;
do while vMatchValues='N' and (vCounter < 5)
  trace Step 5 - Inside do while loop;
  sleep 2000; // 900000 sleep for 15 minutes waiting for data to be ready
  drop field RECORDDT;
  trace Step 6 - Reloading data source to see if updated;
  LOAD RECORDDT;
  SQL SELECT MAX(YDAYDT) AS RECORDDT FROM database.file1;
  Let vRecordDt = peek('RECORDDT');
  Let vCounter = vCounter + 1;
  trace Step 7 - new Recorddt value=$(vRecordDt);
  trace Step 8 - vYesterdayAS400 value=$(vYesterdayAS400);
  trace Step 9 - vCounter=$(vCounter);
  If ($(vRecordDt) = $(vYesterdayAS400)) Then
             Let vMatchValues = 'Y';
  else
            Let vMatchValues = 'N';
  endif
  trace show vmatchvalues = $(vMatchValues);
loop
trace Step 11 - Outside do while loop;
trace Step 12 - vCounter=$(vCounter);
trace Step 13 - Recorddt value=$(vRecordDt);
trace Step 14 - vYesterdayAS400 value=$(vYesterdayAS400);
...additional steps...

And trace for the solution:

Connecting to dataconnection

Connected

Step 1 - Load initial source data

File1 1 lines fetched

Step 2 - vYesterdayAS400 value=1130604

Step 3 - vRecordDt value=1130603

show vmatchvalues = N

Step 4 - About to enter do while loop

Step 5 - Inside do while loop

Step 6 - Reloading data source to see if updated

File1 1 lines fetched

Step 7 - new Recorddt value=1130604

Step 8 - vYesterdayAS400 value=1130604

Step 9 - vCounter=2

show vmatchvalues = Y

Step 11 - Outside do while loop

Step 12 - vCounter=2

Step 13 - Recorddt value=1130604

Step 14 - vYesterdayAS400 value=1130604

...additional data steps...

--- Script Finished ---

So, I still have the question as to why the original DO WHILE with the $(variable) statements didn't work?? I'm glad I have a workable solution, but it's going to bother me. Is it a Qlikview bug?

Re: Waiting for data source to update data while refresh is running

swuehl — Thu, 06 Jun 2013 12:54:16 GMT

I would guess that this will break your logic also:

do while $(vMatchValues) ='N' and (vCounter < 5)

because I think the dollar sign expansion will be only executed once for the DO WHILE statement, not for every loop run. So it will be interpreted as

do while 'N'='N' and (vCounter < 5)

for every loop run, if your initial comparison returns false.

I would have assumed that this is also causing the initial issue, but since you already tried to replace the dollar sign expansion with just the variable name (as indicated in my first post), I don't have a clue right now.

If my assumption is correct, running your script with the data base being up to date should result in no wait time at all.

Regards,

Stefan

Re: Waiting for data source to update data while refresh is running

brennanbd — Thu, 06 Jun 2013 14:24:50 GMT

Your assumption is correct - if the database is updated, it doesn't go through the do while logic at all. Introducing the MatchValues variable is working so I'll close the issue. I'd still be interested to know why the original logic didn't work. Thanks again!