Table Rows Deletion

[#1]

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

[Sakura]

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 ;

[#3]

Hello Ash,

Thank you for your response. Please can you help me further, what will it been if, say, I want to get rid of row 1 and row 5?

Alison

[pathiqvd]

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,

[#5]

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?

[pathiqvd]

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,

[prieper]

If you refer to rownumbers, it might be easier to include in your table, like:

BaseTable:

LOAD

RECNO()               AS RowNo,

.....

FROM ...;

FilteredTable:

NOCONCATENATE LOAD * RESIDENT BaseTable WHERE NOT MATCH (RowNo, 1, 5);

[#8 · Accepted solution]

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!