3 Replies Latest reply: Jul 7, 2015 2:10 AM by Boris Adloff RSS

    Delete selected values

    Kashif Feroz

      Hi everyone.

       

      I would like to delete certain values in a specific pattern. Please refer to the attached picture file.

       

      I want to delete no values from column, delete last value in column 1, delete last 2 values in column 2, delete last 3 values in column... and so on.

       

      Can this be done through qlikview or do i have to do it manually in excel? Thankstest.jpg

        • Re: Delete selected values
          Boris Adloff

          No worries, Kashif, it can be done in Qlikview by loading the data in a loop.

          If I get you right, the script should work with something like this:

           

          yourtablename:

          load

               accidentdays,

               column1,

               rowno() as rownumber

          from

          [yourfilename.xls, etc (need to use the document load assistant here)];

           

          let vRowMax = peek(rownumber, -1);

          let vRow=1;

           

          for $(vRow) to $(vRowMax)

          left join (yourtablename)

               load

                    accidentdays,

                    columname$(vRow)

               resident yourtablename;

              

               let vRow = $(vRow) +1;

          next

           

          I am sure that some of our gurus here would do it in a much more elegant way, but this is how I would adress it.

          You can then either re-export your data from a table report in QV or store it via the script

           

          store * from tablename into tablename.csv (txt);

           

          This command comes very handy when having to export hundreds of csv-files.

            • Re: Delete selected values
              Boris Adloff

              ..oops, correct:

               

              yourtablename:

              load

                   accidentdays,

                   column1,

                   rowno() as rownumber

              from

              [yourfilename.xls, etc (need to use the document load assistant here)];

               

              let vRowMax = peek(rownumber, -1);

              let vRow= $(vRowMax);

              let vColumn= 1;

              let vColMax= [enter number of columns]

               

              for 1 to $(vColMax)

              left join (yourtablename)

              first $(vRow)

                   load

                        accidentdays,

                        columname$(vColumn)

                   resident yourtablename;

               

                   let vColumn = $(vColumn)+1;

                   let vRow = $(vRow) -1;

              next

            • Re: Delete selected values
              shair abbas

              There is an other way around.
              i assume in date column you have the latest date in last cell so what you have to do is first load the table get max date in a variable named 'max_date'  then drop the table..
              again load the table and for each column you can write following conditions
              if(date=max_date,0,column1) as column1

              if(date=date(num(max_date)-1),0,column2) as column2

              if(date=date(num(max_date)-2),0,column3) as column3

               

              and so on.....

               

              hope this might help.