topic Re: Dynamic Delete in QlikView

Dynamic Delete

Tue, 22 Jul 2014 09:47:06 GMT

Hello Everyone,

I have implemented dynamic update to insert a record within a table or field. But I want to delete not all data but data which is last inserted into the table.is this possible by Dynamic Update ?

to delete the field value I used following delete command

DELETE FROM TestTable WHERE -1

where TestTable is the name of the Table.

Re: Dynamic Delete

SunilChauhan — Tue, 22 Jul 2014 09:57:30 GMT

DELETE FROM TestTable WHERE peek('Tablename',-1,'keyfieldnameorID');

Re: Dynamic Delete

SunilChauhan — Tue, 22 Jul 2014 10:01:51 GMT

DELETE FROM TestTable WHERE ID=Max(ID);

Re: Dynamic Delete

Tue, 22 Jul 2014 10:02:52 GMT

it wont work

Re: Dynamic Delete

SunilChauhan — Tue, 22 Jul 2014 10:06:06 GMT

DELETE FROM TestTable WHERE modifieddate=Max(modifieddate);

Re: Dynamic Delete

its_anandrjs — Tue, 22 Jul 2014 10:10:50 GMT

Hi,

I believe DELETE is not supported by qlikview you can achieve this by another resident load table see the example load script

You use this expression in the SQL

DELETE FROM TestTable WHERE -1

Re: Dynamic Delete

Tue, 22 Jul 2014 10:12:06 GMT

but this syntax delete all the table data,I want to delete last inserted data.

Re: Dynamic Delete

its_anandrjs — Tue, 22 Jul 2014 10:17:53 GMT

Try this way suppose there us table and store last record value in the variable r if you have Primary key then use that key

Ex:-

Table1:

LOAD * Inline

[

City,Sales

A, 343454

B, 15482

C, 125659

D, 15823

E, 12563

];

LET vLast = Peek('City' ,-1, 'Table1'); //Here in example last row is E,12563 so we have to delete it from table

NoConcatenate //Then load this resident load without last row

LOAD

City,Sales

Resident Table1

Where not City = '$(vLast)';

DROP Table Table1;

Note:- Load only previous values except the last row.

Hope this helps

Re: Dynamic Delete

Tue, 22 Jul 2014 10:21:06 GMT

This is possible on script editor right,But my requirement is to delete from user interface,I am asking is this possible or not?

Re: Dynamic Delete

its_anandrjs — Tue, 22 Jul 2014 10:22:35 GMT

Hi Bhawana,

Which version of QV you are using.

Thanks

Re: Dynamic Delete

Tue, 22 Jul 2014 10:25:42 GMT

Its 11.20 SR5

Re: Dynamic Delete

its_anandrjs — Tue, 22 Jul 2014 10:38:29 GMT

Correct this is from load script in the front end from the (UI) you can achieve this by creating the flag field in the loasd script means for the last rows and in the UI select without the last records

Ex:-

Load * Inline

[

City,Sales,Flag

A, 343454,0

B, 15482,0

C, 125659,0

D, 15823,0

E, 12563,1

];

And as you say DELETE is work on the script then try like

LET vLast = Peek('City' ,-1, 'Table1');

DELETE FROM TestTable WHERE FieldName = '$(vLast)';

And please read this post

Direct Discovery - can QlikView delete row in database?

Re: Re: Dynamic Delete

SunilChauhan — Tue, 22 Jul 2014 10:40:40 GMT

see the attached

hope this helps