6 Replies Latest reply: Oct 4, 2016 3:37 PM by Matheus Colares RSS

    Remove data .QVD

    Matheus Colares

      Hi folks...

      I want remove a data from .qvd, because is a accumulation data, day by day, i mean, every day the script records a position of stock product, and in March there was a  movimentation was recorded wrong, now i want eliminate this data, like this...I will load all .qvd where date <>(especific date) AND product <> (especific product), as sql...but qlikview read only the first condition, in the order words, the qlikview  eliminates all date(especific date) independent of product...In qlikview the condicion AND dont mean (When the first AND second condition return true)...right?

       

      Somebody know how i can eliminate this record?

       

      thank you!!

        • Re: Remove data .QVD
          Alejandro Fernandez

          You can remove data that way, loading a QVD file in a script and storing it again, overwriting it, Yes you can use a WHERE condition to load and filter your data by date, if you don't have a date field, well maybe it wasn't stored in the first place, but if you do have one you can use an specific date, you will maybe need to use the Num instruction over the field to serialize the Date.

           

          In short, YES you can delete record from a QVD file if you know how to filter the unwanted record.

           

          Regards

          • Re: Remove data .QVD
            Massimo Grossi

            if you want to delete a specific date and product

             

            where not (date = '01/01/2015' and product = 'a');
            • Re: Remove data .QVD
              Matheus Colares

              Thanks by return!!

              Yes, but for example...a have the following situation...

               

              EXAMPLE:

              DATE        PRODUCT          VALUES

              01/02/2016      1                    $ 5,00

              01/02/2016      2                    $ 5,00

              01/02/2016      3                    $ 5,00

              02/02/2016      4                    $ 5,00

              02/02/2016      5                    $ 5,00

              02/02/2016      6                    $ 5,00

              02/02/2016      7                    $ 5,00

              03/02/2016      8                    $ 5,00

              03/02/2016      9                    $ 5,00

              03/02/2016      10                    $ 5,00


              Now I want remove the bolt line, so...


              EXAMPLE2:

              Load * from EXAMPLE.QVD where DATE  <> 01/02/2016 AND PRODUCT <> 1;

               

              STORE EXAMPLE2 INTO EXAMPLE.QVD (QVD);

              DROP TABLE EXAMPLE2;

               

              Basically is this, right?...But i dont know why the result is:

               

              DATE        PRODUCT          VALUES

              02/02/2016      4                    $ 5,00

              02/02/2016      5                    $ 5,00

              02/02/2016      6                    $ 5,00

              02/02/2016      7                    $ 5,00

              03/02/2016      8                    $ 5,00

              03/02/2016      9                    $ 5,00

              03/02/2016      10                  $ 5,00


              The script is eliminating all record with date 01/02/2016, and not only the record with product = 1;

              QlikView is ignoring the second condition...




               





                • Re: Remove data .QVD
                  Massimo Grossi

                  your condition is wrong

                  you load the records where DATE  <> 01/02/2016 AND PRODUCT <> 1;

                  so these 3 records

                  01/02/2016      1                    $ 5,00

                  01/02/2016      2                    $ 5,00

                  01/02/2016      3                    $ 5,00

                  aren't loaded because the date is 01/02/2016

                   

                   

                  try

                  where not (DATE  = '01/02/2016' and PRODUCT = 1);
                    • Re: Remove data .QVD
                      Massimo Grossi

                      S:

                      load * inline [

                      DATE    ,    PRODUCT        ,  VALUES

                      01/02/2016,      1 ,                   $ 5,00

                      01/02/2016 ,     2  ,                  $ 5,00

                      01/02/2016  ,    3   ,                 $ 5,00

                      02/02/2016   ,   4    ,                $ 5,00

                      02/02/2016    ,  5     ,               $ 5,00

                      02/02/2016     , 6      ,              $ 5,00

                      02/02/2016 ,     7       ,             $ 5,00

                      03/02/2016  ,    8        ,            $ 5,00

                      03/02/2016   ,   9         ,           $ 5,00

                      03/02/2016    ,  10         ,           $ 5,00

                      ];

                      store S into S.qvd (qvd);

                       

                      drop table S;

                       

                      // read from qvd

                      S:

                      load *

                      from S.qvd (qvd)

                      where  not (DATE  = '01/02/2016' and PRODUCT = 1);