6 Replies Latest reply: Jun 6, 2013 10:24 AM by Billy Brennan RSS

    Waiting for data source to update data while refresh is running

    Billy Brennan

      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
          Stefan Wühl

          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
              Billy Brennan

              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
                  Stefan Wühl

                  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
                      Billy Brennan

                      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
                          Stefan Wühl

                          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