Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
sifatnabil
Specialist
Specialist

Delete rows based on condition

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.

1 Solution

Accepted Solutions
rwunderlich
MVP & Luminary
MVP & Luminary

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

View solution in original post

4 Replies
tresesco
MVP
MVP

Try like:

Tab:

Load

          A,

          B,

          C,

From <>;

Inner Join

Load

          Max(A) as A

Resident Tab Group By B;

rwunderlich
MVP & Luminary
MVP & Luminary

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

View solution in original post

sifatnabil
Specialist
Specialist

Hi,

I've tried this and it worked. Thanks!

sunilkumarqv
Specialist II
Specialist II

Hi Sifat,

make it correct and close the thread