8 Replies Latest reply: Jun 21, 2011 12:58 AM by At titude RSS

    Appending each week data in a QVD!

      Hi All

       

      Can someone please help me out to do following thing please. With some example could be much better!

       

      1. On weekly basis I will be getting a file called status.txt. The file name will not change.

       

      2. It should be appended each week.

       

      3. Need to store the current week status(status.txt) in a qvd and then load from QVD followed by loading from the new txt file.

       

      Hope to receive reply from some one soon.

       

      Thanks

       

      Attitude

        • Appending each week data in a QVD!
          Steve Dark

          The simplest way is to put a timestamp into the QVD file name and then load from it with a wildcard.  For example, to create:

           

          let vDateStamp = date(today(), 'YYYYMMDD');

           

          DataFile:

          LOAD
               *

          FROM WeeklyFile.txt

          (txt, codepage is 1252, no labels, delimiter is ',', msq);

           

          STORE DataFile INTO Data\DataFile_$(vDateStamp).qvd (qvd);

           

          And then when you come to load from a folder full of dated QVD files it would simply be:

           

          Data:

          LOAD

               *

          FROM Data\DataFile_*.qvd (qvd);

           

          This assumes that there is no duplication of rows between weekly files, and all files arrive in exactly the same format.  I would recommend not using * to load fields - and reference them all explicitly.  This will mean if a file does come in the wrong format the create of the QVD will fail, rather than the load from QVD.

           

          Loading from many files will mean that the load will be slightly slower than from a single QVD file and the QVD's will take up slightly more space on the disk.  You could build a routine that occaisionally loads all QVD's with a wildcard (as above) and then writes out a new QVD with data from all the source files in.  The source files can then be deleted.

           

          If there is a unique key on each row then you can add bulletproofing around importing the same row more than once by using WHERE EXISTS.  If there is no key you just need to be very careful.

           

          Hope that helps.

           

          - Steve

            • Appending each week data in a QVD!

              Hi Steve

               

              Thanks! This is not the way client has requested for. I want to load everything in a single qvd and then append it everyweek. Managing multiple QVD's will be very difficult and as said it will affect the performance as well. Requesting you to suggest some thing as per their requirement.

               

              If you have any doubts please let me know on that. I will try to post you more details on this soon.

               

              Thanks

               

              Attitude

                • Re: Appending each week data in a QVD!
                  John Witherspoon

                  MyData:
                  LOAD *
                  FROM MyWeeklyFile.txt (blah blah blah)
                  ;
                  CONCATENATE (Data)
                  LOAD *
                  FROM MyQVD.qvd (QVD)
                  ;
                  STORE MyData INTO MyQVD.qvd (QVD)
                  ;

                   

                  As with Steve's suggestion, I'm assuming no duplication of rows.  You may need to reference a key, or append the week onto the data as a field, or who knows what else.

                    • Re: Appending each week data in a QVD!

                      Thanks John

                       

                      I will try and let you know on that but I feel it may not work as I wants to do something like below.

                       

                      http://community.qlik.com/message/125391#125391

                       

                      Regards

                       

                      Attitude

                      • Re: Appending each week data in a QVD!

                        Hi

                         

                        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: Appending each week data in a 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

                            • Re: Appending each week data in a QVD!
                              John Witherspoon

                              Something like this, perhaps, but there are probably syntax errors.  The idea is to load from the QVD, set a variable to the max date on the QVD, and then use that variable in the subsequent loads.  If there is no QVD, you'll use date(0), so anything from 1900 on.  I also recommend using a login timestamp instead of date if that makes sense for your application.

                               

                              DIRECTORY C:\;

                              IF  filetime('Login.qvd')>0 THEN
                                  Login:
                                  LOAD LoginDate, User, Product
                                  FROM Login.qvd (QVD);
                                  MaxDate:
                                  LOAD max(Date) as MaxDate;
                                  LOAD date(fieldvalue('LoginDate',recno())) as Date
                                  AUTOGENERATE fieldvaluecount('LoginDate');
                                  LET vMaxDate = peek('MaxDate');

                                  DROP TABLE MaxDate;
                              ELSE
                                  LET vMaxDate = 0;
                              END IF

                              Login: // will concatenate if already exists, else will create
                              LOAD @1 as LoginDate,
                                   @2 as User,
                                   @3 as Product
                              FROM Status.txt (txt, codepage is 1252, no labels, delimiter is ',', msq)
                              WHERE Date(@1) > date($(vMaxDate));

                              CONCATENATE (Login)

                              LOAD Product,
                                   LoginDate,
                                   User
                              FROM $(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels)
                              WHERE Date(@1) > date($(vMaxDate));

                              STORE Login INTO Login.qvd (QVD);