7 Replies Latest reply: Jul 27, 2017 6:36 AM by Alison Cooke RSS

    Table Rows Deletion

    Alison Cooke

      Hello Everyone,

       

      I have a table uploaded on Qlik. I want to delete the first row and the last row.

       

      I have deleted the first row in the following way:

       

      FROM [lib://Donation Sheets]

      (html, codepage is 1252, embedded labels, table is @1)WHERE RecNo() > 1;

       

      However, I am unsure about deleting the final row also. How should this be done?

       

      Thank you in advance,

      Alison

        • Re: Table Rows Deletion
          Ash M

          I think you need to get the count of the rows and use that in your where condition.

          WHERE RecNo() < select Count of rows from the table ;

          • Re: Table Rows Deletion
            lakshmipathi p

            Hi,

            Try Like this,

             

            AC:

            load * Inline [

            id, sales1

            1,10

            2,20

            3,40

            4,30

            5,15

            ];

             

            count:

            load Count('id') as countid Resident AC;

             

            Let vcount=Peek('countid',0,'count');

             

             

            AC1:

            Load id as idtest, sales1 as salestest Resident AC Where RecNo()<>1 and RecNo()<>$(vcount);

             

             

            Regards,

              • Re: Table Rows Deletion
                Alison Cooke

                Hello Lakshmipathi,

                 

                Thank you for your suggestion. It is not creating a inline table a bit over-complicated? Especially seeing as I can get rid of a single row just using  WHERE RecNo() > 1;? Isn't there a simpler expression?

                  • Re: Table Rows Deletion
                    lakshmipathi p

                    Hi,

                     

                      First load the data from database or excel anything ok

                     

                    AC:

                    select * from table name or load * from excel;

                     

                    after count rows  like below

                    count:

                    load Count('id') as countid Resident AC;

                     

                    next use this

                    Let vcount=Peek('countid',0,'count');

                     

                    once again load full table or resident and apply where condition

                     

                    AC1:

                    Load id as idtest, sales1 as salestest Resident AC Where RecNo()<>1 and RecNo()<>$(vcount);

                     

                     

                     

                    Regards,

                • Re: Table Rows Deletion
                  Alison Cooke

                  I have been able to solve the issue by doing the following:

                   

                  FROM [lib://Merchandise Sheet]

                  (html, codepage is 1252, embedded labels, table is @1) Where RecNo()<> 1 AND 5.

                   

                   

                  I only needed to use the AND function. As I thought, it was very simple!