topic Re: Delete rows based on condition in QlikView

Delete rows based on condition

sifatnabil — Mon, 10 Mar 2014 05:05:05 GMT

Hi all,

In the load script, I have a table with several columns, but want to work with 2 columns, "Version", and "ID".

The same ID values populate with different versions; for example: ID=1234 resides in 3 rows, with Version=1, 2, and 3. I wish to delete the rows with the lower versions and keep the row with the latest version for each ID.

This would require finding the maximum value of "Version" for that particular ID, and then deleting the entire row. How could I achieve this in the load script?

Thank you.

Re: Delete rows based on condition

tresB — Mon, 10 Mar 2014 05:14:08 GMT

Try like:

Tab:

Load

From <>;

Inner Join

Load

Max(A) as A

Resident Tab Group By B;

Re: Delete rows based on condition

rwunderlich — Mon, 10 Mar 2014 05:15:17 GMT

Like this:

data:

LOAD * INLINE [

ID, Version, Value

1, 1, 100

1, 2, 200

2, 1, 300

3, 3, 150

3, 4, 200

]

;

INNER JOIN (data)

LOAD

ID,

max(Version) as Version

RESIDENT data

GROUP BY ID

;

-Rob

http://robwunderlich.com

http://masterssummit.com

Re: Delete rows based on condition

sifatnabil — Mon, 10 Mar 2014 07:44:20 GMT

Hi,

I've tried this and it worked. Thanks!

Re: Delete rows based on condition

sunilkumarqv — Mon, 10 Mar 2014 07:56:01 GMT

Hi Sifat,

make it correct and close the thread