topic Re: Table Rows Deletion in App Development

Table Rows Deletion

Wed, 26 Jul 2017 15:41:01 GMT

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

Sakura — Wed, 26 Jul 2017 16:11:49 GMT

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

Thu, 27 Jul 2017 08:35:01 GMT

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

Re: Table Rows Deletion

pathiqvd — Thu, 27 Jul 2017 09:01:39 GMT

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

Thu, 27 Jul 2017 09:10:00 GMT

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

pathiqvd — Thu, 27 Jul 2017 09:24:20 GMT

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

prieper — Thu, 27 Jul 2017 09:57:42 GMT

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

Re: Table Rows Deletion

Thu, 27 Jul 2017 10:36:48 GMT

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!