5 Replies Latest reply: Dec 6, 2011 4:00 AM by Anne Duffy RSS

    An Update load not Refreshing

      Hi Guys

       

      I load in from an excel sheet, called [Data 12]

       

      This is a manual Spreadsheet that is over typed everyday.

       

      So today on a Monday I come in and Reload Qlikview this pulls in all the data into QV from

      [Data 12] - this will have all Fridays info.

      Date                            Meat                                Number

      1/1/11                          Beef                                20

      1/1/11                          Chicken                          10

      1/1/11                          Beef                                17

         

      Throughout the working day on Monday the team will delete and over type the data into

      [Data 12],

       

      Date                            Meat                                Number

      4/1/11                          Beef                                11

      4/1/11                          Chicken                           5

      4/1/11                          Beef                                20

       

       

      Is it possible that when I reload tomorrow I can retain the data from Friday and in addition take the data from Monday so I have :

      Date                            Meat                                Number

      1/1/11                          Beef                                20

      1/1/11                          Chicken                          10

      1/1/11                          Beef                                17

      4/1/11                          Beef                                11

      4/1/11                          Chicken                           5

      4/1/11                          Beef                                20

       

       

      Currently when I reload I am OVERWRITING the Data from Friday so am just getting

       

      Date                            Meat                                Number

      4/1/11                          Beef                                11

      4/1/11                          Chicken                           5

      4/1/11                          Beef                                20

       

       

      Would appreciate any assistance

       

      Thanks

      A

        • Re: An Update load not Refreshing
          Miguel Angel Baeyens de Arce

          Hi,

           

          Store each day data into a QVD file. This storage can be incremental (only one QVD file with more records each day) or multiple files, one for each day, so you can chose later on which of the QVD files will load.

           

          LET vFileDate = Date(Today(), 'YYYYMMDD');
          
          DailyData:
          LOAD * 
          FROM Excel.xls
          (biff, explicit labels, table is Sheet1$);
          
          STORE DailyData INTO $(vFileDate).qvd (qvd);
          
          DROP TABLE DailyData;
          
          AllData:
          LOAD Date,
               Meat,
               Number
          FROM *.qvd (qvd);
          

           

          The first line creates a variable with no special characters to be used as the name of the file. The result will be files named like 20111104.qvd, 20111205.qvd and so on.

           

          The DailyData table loads only data from today, irrespective the changes, and stores into a file like above.

           

          The AllData table loops in the specified folder and loads all QVD files, so you have data from all days. Since all files have the same number and name of fields, all records are concatenated automatically which is very convenient in this case.

           

          Now you can modify the code to fir your needs, load only when not exists, and so. If needed, you can easily create a loop to load only the last month, last year or any given period with i.e.: a For Next loop.

           

          Hope that gives you an idea.

           

          Miguel