Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Table Rows Deletion

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

1 Solution

Accepted Solutions
Not applicable
Author

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!

View solution in original post

7 Replies
Sakura
Creator
Creator

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 ;

Not applicable
Author

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
Creator III
Creator III

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,

Not applicable
Author

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
Creator III
Creator III

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
Master II
Master II

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);

Not applicable
Author

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!