topic Re: Remove unwanted rows in a table in QlikView

Remove unwanted rows in a table

Tue, 25 Aug 2015 15:03:03 GMT

Hi,

I have a straight table with content as shown below

CID	LID	Desc	StartDate	EndDate
1	1	Test	30/06/2012	30/06/2013
1	2	Test2	31/10/2013	31/10/2014
2	1	Test	30/11/2008	30/11/2013
2	2	Test2	31/07/2014	31/07/2015
3	1	Test	29/02/2008	28/02/2013
3	2	Test2	31/10/2013	31/10/2014
4	1	Test	31/08/2012	31/08/2013
4	2	Test2	31/08/2014	31/08/2015

I would like to keep only one row for each CID, i.e the latest row of data.

Can someone please let me know how to do this in the front end and not in the script?

Thanks

Re: Remove unwanted rows in a table

Qrishna — Tue, 25 Aug 2015 15:07:47 GMT

You can try using FirstSortedValue() function : FirstSortedValue(CID, StartDate).

let me know if that doesnt work

Re: Remove unwanted rows in a table

Tue, 25 Aug 2015 15:08:56 GMT

Razor , While leading the data, just load the data what you need.

Data:

LOAD

CID,

LID,

DESC,

StartDate,

EndDate,

From Source;

Inner Join(DATA)

LOAD

CID,

Max(StartDate) AS StartDate

Resident DATA

Group By CID;

The above script keeps only latest record CID based on StartDate.

Re: Remove unwanted rows in a table

patroser — Tue, 25 Aug 2015 15:12:32 GMT

You could work with max()

Taking CID, LID, Desc and StartDate as Dimensions and max(EndDate) as expression ends up in a straight table just holding the values of the latest EndDate.

Re: Remove unwanted rows in a table

maxgro — Tue, 25 Aug 2015 15:17:48 GMT

Re: Remove unwanted rows in a table

Tue, 25 Aug 2015 15:24:50 GMT

That's working now. Thanks to everyone, especially Krishna and Massimo