2 Replies Latest reply: Jun 20, 2011 7:43 AM by At titude RSS

    Loading data from text file and sessions log on weekly basis in a qvd. Only new data to be appended to the QVD.

      Hi All

       

      I have table called as Login. In this table file will be loaded from the txt file and from qlikview sessions log file. First we would want to store the data from both(txt file and sessions log) in a qvd. Then we would like to concatenate and store it in the qvd the every new text file and data from the sessions log which is not there in the previous qvd.

       

      Login:

       

      //Following text file should be stored in a QVD
      //Everyweek we will receive on text file
      //File name will remain same(status.txt)
      //Everyweek new text file should be appended to QVD data(Previous data).


      LOAD @1 as LoginDate,
           @2 as User,
           @3 as Product
      FROM
      [C:\status.txt]
      (txt, codepage is 1252, no labels, delimiter is ',', msq);

       

      //Data is loaded in the below table from the sessions log
      //It has to be concatenated to the above table
      //Everytime the data will be loaded from the same path
      //Like above every week new data has to be appended like above

       

      Load

      LoginDate,
      User,
      Product

      FROM $(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels);

       

      To work on the above requirement. We got the request to do something like below. Could you please review and let me know whether it will workout or not please. I am little confused here! Now sure from where to start.

       

      LOAD * FROM $(folder)Login (qvd);

       

      //Max(LoginDate)  or last reload from the text file
      //Max(LoginDate)  or last reload from the sessions log load

       

      CONCATENATE

       

      Load
      LoginDate,
      User,
      Product
      FROM $(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels)
      Where LoginDate > Max(LoginDate)  or last reload from the sessions log load;

       

      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 @1 > Max(LoginDate)  or last reload from the text file;

       

      STORE Login into $(vfolder)Login.QVD;

        • Re: Loading data from text file and sessions log on weekly basis in a qvd. Only new data to be appended to the QVD.

          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); 
          

           

           

          Thanks

           

          Atttitude

          • Re: Loading data from text file and sessions log on weekly basis in a qvd. Only new data to be appended to the QVD.

            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