4 Replies Latest reply: Dec 24, 2016 10:45 PM by Jayant Tibhe RSS

    How to remove certain table year = 2016 data ?

    PAUL YEO

      Hi All

       

       

      If i select year 2015 2014 , and

      If i using File - > Reduce data - > Keep possible value.

      it will delete year 2016 data and keep only 2015 and 2014 data.

      This will delete all table year 2016 daya. which is not what i want.

       

       

      I want to only delete certain table it is possible ?

       

       

      Paul

        • Re: How to remove certain table year = 2016 data ?
          Sunny Talwar

          Not sure your question is very clear Paul. Can you try to explain it further?

            • Re: How to remove certain table year = 2016 data ?
              PAUL YEO

              Hi Sunny

               

              My issue is when I prepare my slide for annual sales meeting during Feb 13.

               

              If I prepare the slide now it will not capture 2016 full year sales.

               

              If I prepare during mid of Jan 2017 , it will appear 2017 Jan sales .

               

              As I only want the sales only display up to 31 Dec 2016.

               

              So only way for me is to make use of data reduce . This will remove all 2017 sales when I do it on 13 Feb.

               

              But the issue is it will also remove my sales target figure for 2017.

               

              As sales target for 2017 figure I need it for present.

               

              So only way I can do is I want to learn how to remove year 2017 data by TABLE or by field name using script.

               

              For example look for sales table and remove those data year > 2016  :-

               

              Sales :

              Load *

              Resident where year > '2016'

               

              and for sales target I try to create another table name as TARGET

               

              wish you merry x'mas and happy new year.

               

              Paul Yeo

              DIrector

              TDS Technology (S) P/L

              Whatsapp +659:261804

                • Re: How to remove certain table year = 2016 data ?
                  Jayant Tibhe

                  Hi Paul,

                  I can see many Year fields in your QVW so not sure which table and which year fields you dont need for Year =2016.

                  There are 2 simple ways to achieve it  - with Where clause to omit Year 2016 OR can use Match function to achieve the same  : Suppose you have following table  :   (Also, find attached QVW file.)

                   

                  Table1:

                  LOAD * INLINE [

                      Test, Year

                      1, 2013

                      2, 2014

                      3, 2015

                      4, 2016

                      5, 2017

                  ];

                  NoConcatenate

                  Table2:

                  Load *

                  Resident Table1

                  where

                  Not Match(Year,'2016')

                  // OR You can also use following statement in where clause - Either use

                  //Year > 2016 OR Year <2016

                  ;

                  Drop Table Table1;

                   

                   

                   

                  Thanks

                  Jayant Tibhe