3 Replies Latest reply: Jun 20, 2011 7:37 AM by At titude RSS

    If QVD already exists then I don't want to execute piece of code!

      Hi All

       

      If the QVD already exists in the then I don't want to execute the piece of code which is there in between the block (/////). May I know how to do that please!

       

      //////////////////////////////////////////////////////////////////
      Login:
      
      LOAD @1 as LoginDate, 
           @2 as User, 
           @3 as Product
           //,
           //ReloadTime() as LastReload
      FROM
      [C:\status.txt]
      (txt, codepage is 1252, no labels, delimiter is ',', msq);
      
      Store Logon into Login.qvd (qvd);
      
      
      Drop table Login;
      ///////////////////////////////////////////////////////////////////
      Login:
      
      
      LOAD LoginDate, 
           User 
           Product
      FROM
      [C:\Logon.qvd]
      (qvd);
      
      
      CONCATENATE
      
      
      LOAD @1 as LoginDate, 
           @2 as User, 
           @3 as Product
      FROM
      [C:\status.txt]
      (txt, codepage is 1252, no labels, delimiter is ',', msq)
      ;
      
      
      Store Login into Login.qvd (qvd);
      


      Thanks

       

      Attitude

        • If QVD already exists then I don't want to execute piece of code!
          Kaushik Solanki

          Hi,

           

              Below given is nothing but the script for incremental load. This will help you to recognise that QVD is already  generated or not.

              

          LET vQVDPath = 'Qvd\'; // Set QVD storage Directory

          SET vOffset = '00:15:00';

          LET vUTC = UTC();

          LET vExecTime = replace(timestamp(vUTC-vOffset, 'YYYY-MM-DDXhh:mm:ssZ'), 'X','T');

           

          // SalesForce.com uses a timestamp format of ‘YYYY-MM-DDThh:mm:ssZ’, hence the need for the replace statement here.

           

          SET vLastExecTime = 0; // resetting vLastExecTime

           

          // As long as a QVD already exists (the isnull() check), find the latest timestamp for modified records. This will be used to generate the delta set.

           

          if not isnull(QVDCreateTime('$(vQVDPath)<tablename>.qvd')) then

          LoadTime:

          Load Max(LastModifiedDate) as LastModifiedDate // Or max(CreatedDate)

          From $(vQVDPath)<tablename>.qvd (qvd);

          Let vLastExecTime = replace(timestamp(peek('LastModifiedDate',0,'LoadTime'),'YYYY-MMDDXhh:mm:ssZ'), 'X', 'T');

          Drop Table LoadTime;

          end if

          SQL SELECT Id,

          FROM <tablename>

          WHERE LastModifiedDate >=$(vLastExecTime) and LastModifiedDate < $(vExecTime);

           

          // Check to see if this is the first reload. If it is, skip this step

           

          if Not isnull(QvdCreateTime('$(vQVDPath)<tablename>.qvd')) then

          Concatenate (<tablename>)

          LOAD *

          FROM $(vQVDPath)<tablename>.qvd (qvd)

          WHERE Not(Exists (Id));

          end if

           

          //If data exists within table, store to QVD.

             

          if NoOfRows('<tablename>') > 0 then

          STORE <tablename> INTO $(vQVDPath)<tablename>.qvd;

          Drop Table <tablename>;

          end if

           

           

                Hope this will help you.

           

          Regards,

          Kaushik Solanki

            • Re: If QVD already exists then I don't want to execute piece of code!

              Hi Koushik

               

              I didn't try with the solution that you have provided as I need to understand everything before implementing in mine. I am trying something like below. If I am loading it for the first time as there won't be any Login.qvd created. Could which is inside the block(/////) will be executed. For next time onwards whatever the code which is there after "end if" will be executed(Which will concatenate the data into the login.qvd from the text file and as well from the session log).

               

              One which I still wanted to do it here is I want to check the max(LoginDate) which is loaded from the text file and max(LoginDate) which is loaded from the sessions qvd. Both is there now in a single qvd(Login.qvd). Using this two max(LoginDate) I want to concatenate the data in to the Login.qvd which is there in the 2nd block.

               

              Please guide me on how to to get the max(LoginDate) from Text file and Session log which can be used in the 2nd block. I think to get the Max(LoginDate) we need to write where condition to pull the Max(LoginDate) from the one which is loaded in the text file and Sessions Log.

               

               

              /**************************************** 1st Block*************************************/
              
              //////////////////////
              
              if isnull(filetime('C:\Login.qvd')) then
              
              Login:
              
              LOAD @1 as LoginDate, 
                   @2 as User, 
                   @3 as Product
              FROM
              [C:\Status.txt]
              (txt, codepage is 1252, no labels, delimiter is ',', msq);
              
              Concatenate
              
              LOAD Product, 
                   LoginDate,
                   User
              FROM
              $(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels);
              Store Login into Login.qvd (qvd);
              
              Drop table Logon;
              
              end if;
              
              ////////////////////////
              /**************************************** 2st Block*************************************/
              
              Logon:
              
              LOAD LoginDate, 
                   User, 
                   Product
              FROM
              [C:\Logon.qvd]
              (qvd);
              
              CONCATENATE
              
              LOAD @1 as LoginDate, 
                   @2 as User, 
                   @3 as Product
              FROM
              [C:\Status.txt]
              (txt, codepage is 1252, no labels, delimiter is ',', msq)
              ;
              
              CONCATENATE
              
              LOAD Product, 
                   LoginDate,
                   User
              FROM
              $(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels);
              
              Store Logon into Logon.qvd (qvd);
              
              • Re: If QVD already exists then I don't want to execute piece of code!

                Hi

                 

                I want to load only those data which is not available in the WHERE condition of the 2nd block. I tried to store the max(logindate) of both text file and session log in a varialble but it didn't work. Also tried to load the max(LoginDate) from both the text file and sessions log in a seperate table of a field but it didn't work either.

                 

                Can some please do the required change in the 2nd block of my code so that it store only those rows which is not available in the existing qvd(I need your help where the text is in BOLD letters in 2nd block). This is urgent requirement! Hope you can understand!

                 

                 

                /**************************************** 1st Block*************************************/
                //////////////////////
                if isnull(filetime('C:\Login.qvd')) then
                
                Login:
                
                LOAD @1 as LoginDate, 
                     @2 as User, 
                     @3 as Product
                FROM
                [C:\Status.txt]
                (txt, codepage is 1252, no labels, delimiter is ',', msq);
                
                Concatenate
                
                LOAD Product, 
                     LoginDate,
                     User
                FROM
                $(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels);
                Store Login into Login.qvd (qvd);
                
                Drop table Logon;
                
                end if;
                ////////////////////////
                /**************************************** 2nd Block*************************************/
                Logon:
                
                LOAD LoginDate, 
                     User, 
                     Product
                FROM
                [C:\Logon.qvd]
                (qvd);
                
                CONCATENATE
                
                LOAD @1 as LoginDate, 
                     @2 as User, 
                     @3 as Product
                FROM
                [C:\Status.txt]
                (txt, codepage is 1252, no labels, delimiter is ',', msq)
                WHERE Date(@1) > Max(LoginDate) of text file;
                  
                CONCATENATE
                
                LOAD Product, 
                     LoginDate,
                     User
                FROM
                $(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels)
                WHERE Date(@1) > Max(LoginDate) of session log;
                
                Store Logon into Logon.qvd (qvd); 
                
                

                 

                Thanks

                 

                Attitude